# help with countif variant with multiple criteria

#### bluefish44

##### Board Regular
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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>

thanks Weazel - do you know if this is possible without an array formula?

Replies
0
Views
470
Replies
4
Views
183
Replies
3
Views
291
Replies
1
Views
551
Replies
2
Views
403

1,196,254
Messages
6,014,274
Members
441,810
Latest member
LouLou1234

### 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.

### Which adblocker are you using?

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

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