help with countif variant with multiple criteria

bluefish44

Board Regular
Joined
Apr 3, 2009
Messages
190
I'm trying to come up with a variant of a countif formula:
I want to look at column A and see how many times a unique value occurs, in the example below, John occurs 5 times.
Then I want to see how many unique values occur related to John in column B, in this example its 3 (since apple appears twice, pear appears twice, and peach appears once there are only 3 unique values.I want to put that value, in this case 3, in column C in every row that it says John. I'd like to be able to put the formula in cell C2 and drag down as many entries as there are?

Thanks in advance for any help!

A B C(formula)
John apple 3
John pear 3
John apple 3
John peach 3
Bill orange 1
Bill orange 1
Bill orange 1
Frank pear 2
Frank apple 2
John pear 3
Frank apple 2
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
maybe something like...

Excel 2013
ABC
2Johnapple3
3Johnpear3
4Johnapple3
5Johnpeach3
6Billorange1
7Billorange1
8Billorange1
9Frankpear2
10Frankapple2
11Johnpear3
12Frankapple2

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
C2{=SUM(IF(FREQUENCY(IF($A$2:$A$12<>"",IF($A$2:$A$12=A2,MATCH($B$2:$B$12,$B$2:$B$12,0))),ROW($A$2:$A$12)-ROW($A$2)+1),1))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top