Count If with multiple criteria and unique values

[dixon1983]

Hi,
I dont know if this is possible or not but thought I'd ask anyway.

I have a sheet of data and want to run a count on the values in Column A with the following criteria:

- If the value in column B is repeated then the value in column A can only be counted once...
- Column C must equal 'M'.


Example:
A.....B.....C
1.....42....M
1.....42....M
2.....41....A
2.....41....M
1.....56....M


In this example 1 would be counted 2 times, and 2 would be counted once.

I have about 15 sheets of data like this and would like a master sheet to look into each sheet and give me a count of each so id prefer to not use the advanced filter => unique variables option if possible.

Any help would be greatly appreciated.

Dixon.

 

[Aladin Akyurek]

My data is in two columns, A = list of values, B = list of names. How do I count the unique number of values in column A, that match a name in column B?

Thank You! This is a great site.

What do you have in A? Text or numbers?

 

[tbecker3]

What do you have in A? Text or numbers?

Format says General, this is a sample 65100095100320, stored as =T("65100095100320") in the cell.

 

[Aladin Akyurek]

Format says General, this is a sample 65100095100320, stored as =T("65100095100320") in the cell.

In C2 control+shift+enter, not just enter, and copy down:

=IF(ISNUMBER(MATCH($B2,$B$1:B1,0)),VLOOKUP($B2,$B$1:C1,2,0),
    SUM(IF(FREQUENCY(IF(1-($A$2:$A$400=""),IF($B$2:$$400=$B2,
    MATCH($A$2:$A$400,$A$2:$A$400,0))),ROW($A$2:$A$400)-ROW($A$2)+1),1)))

 

[tbecker3]

So if column a is actually Q, and B is actually AP, and I need the results on another sheet in the workbook, cell D40, how does that change the formula?

 

[tbecker3]

I use this formula =SUMPRODUCT(1/COUNTIF(GPI_Value,GPI_Value)) for the unique count of the A column. Can I add a condition, If, Match, or ? to this formula for column B to only include MACCL1, which is the name in range Client_Price_Type ?

 

[Aladin Akyurek]

So if column a is actually Q, and B is actually AP, and I need the results on another sheet in the workbook, cell D40, how does that change the formula?

A >> Q, B >> AP

I use this formula =SUMPRODUCT(1/COUNTIF(GPI_Value,GPI_Value)) for the unique count of the A column. Can I add a condition, If, Match, or ? to this formula for column B to only include MACCL1, which is the name in range Client_Price_Type ?

Now GPI_Value and back again to A and B...

In D40 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($A$2:$A$400=""),IF($B$2:$$400="MACCL1",
    MATCH($A$2:$A$400,$A$2:$A$400,0))),ROW($A$2:$A$400)-ROW($A$2)+1),1)))

 

[tbecker3]

I get a Value error, "A value used in formula is of the wrong data type" I think it may be the "MACCL1", because it is not a value, but text, or alpha. So can this be fixed?

 

[Aladin Akyurek]

I get a Value error, "A value used in formula is of the wrong data type" I think it may be the "MACCL1", because it is not a value, but text, or alpha. So can this be fixed?

A typo...Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($A$2:$A$400=""),IF($B$2:$B$400="MACCL1", MATCH($A$2:$A$400,$A$2:$A$400,0))),ROW($A$2:$A$400)-ROW($A$2)+1),1)))

 

[tbecker3]

Actually I caught that and fixed as you have. Still Value error. Also, Control+shift+enter does nothing.

 

[Aladin Akyurek]

Actually I caught that and fixed as you have. Still Value error. Also, Control+shift+enter does nothing.

Control+shift+enter means: Press the control and the shift keys at the same time while you hit the enter key. When done properly, Excel itself puts a pair of { and } round the formula:

=SUM(IF(FREQUENCY(IF(1-($A$2:$A$400=""),IF($B$2:$B$400="MACCL1",MATCH($A$2:$A$400,$A$2:$A$400,0))),ROW($A$2:$A$400)-ROW($A$2)+1),1))

Since you get a #VALUE! error, the chances are that the control+shift+enter did not succeed as it should. Please try again...