Here's the set of data I am struggling with:

Column A Column B
12345 1
12345 2
12345 3
12345 1
12345 2
12345 3
12346 1
12346 2
12346 1
12346 2

Am trying to write a formula that would count duplicate records in
column B for the group of records in Column A. For e.g. for record
12345 in column A, 1 in column B gets repeated twice and so on and so
forth or in other words, for record 12345 in column A, group 1,2,3 in
column B gets repeated twice.

Is there a formula that would help me derive that? Pleasseee help, have
been struggling with this since last two days and finally need to pick
your excel brains on this.

Thank You.
Parekh


--
Mparekh
------------------------------------------------------------------------
Mparekh's Profile: http://www.excelforum.com/member.php...o&userid=14999
View this thread: http://www.excelforum.com/showthread...hreadid=495089

Hi,

You may try the following array formula (Ctrl+Shift+Enter). the data is in
the format below (in range A1:B7)

12345 1
12345 2
12345 3
12345 1
12345 2
12345 3
12345 1

In A9 enter, 12345 and in B9 enter 1. in C9, enter the following array
formula COUNT(IF(($A$1:$A$7=A9)*($B$1:$B$7=B9),B1:B7))

You may chage the value in B9 to 2 or 3.

Regards,



"Mparekh" wrote:


Here's the set of data I am struggling with:

Column A Column B
12345 1
12345 2
12345 3
12345 1
12345 2
12345 3
12346 1
12346 2
12346 1
12346 2

Am trying to write a formula that would count duplicate records in
column B for the group of records in Column A. For e.g. for record
12345 in column A, 1 in column B gets repeated twice and so on and so
forth or in other words, for record 12345 in column A, group 1,2,3 in
column B gets repeated twice.

Is there a formula that would help me derive that? Pleasseee help, have
been struggling with this since last two days and finally need to pick
your excel brains on this.

Thank You.
Parekh


--
Mparekh
------------------------------------------------------------------------
Mparekh's Profile: http://www.excelforum.com/member.php...o&userid=14999
View this thread: http://www.excelforum.com/showthread...hreadid=495089

If you want something that looks like the following:

Count of Part B Part B
Part A 1 2 3 (blank) Grand Total
12345 2 2 2 6
12346 2 2 4
(blank)
Grand Total 4 4 2 10

I put Part A at the top of Column A, Part B at the top of Column.
You can do this very easily in a PIVOT table.
I put part A as the row info. Part B as the column and data field.
Try it a couple of different ways to experiment with how you want to
see the info.


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=495089

Ashish, Thank you for the formula. The problem I am facing is, Column A
has various groups of data against which Column B has sub groups of
data, as I had listed earlier. So it would not be possible for me to
enter 12345 in A9 and 1 in B9. That was just an example to be more
explicit on what I am trying to do. I would need an array formula which
would look at data in Column A then recurring data sub groups in Column
B and count those sub groups in Column B and display the result in
Column C.

Pivot tables would not help me much at this point as I need to use the
data in Column C for further calculations in the spreadsheet.

Thank you in advance.


--
Mparekh
------------------------------------------------------------------------
Mparekh's Profile: http://www.excelforum.com/member.php...o&userid=14999
View this thread: http://www.excelforum.com/showthread...hreadid=495089

Ashish, Thanks! I modified your suggested formula a bit and it worked.

Thanks again.


--
Mparekh
------------------------------------------------------------------------
Mparekh's Profile: http://www.excelforum.com/member.php...o&userid=14999
View this thread: http://www.excelforum.com/showthread...hreadid=495089