Help with Countif

[boop]

Hi all,
In the sample data below, I need to find the number of unique codes in column A coresponding to "M" in column B. Is there a simple formula I can use? I'm using Excel 2010 if that changes anything.
A B
1 1233 M
2 4562 M
3 1233 M
4 7625 S
5 7352 S
6 4562 M
7 2447 S

Thanks!

 

[Tom Urtis]

One way, as an array formula:

<code>=SUM(IF(FREQUENCY(IF((B1:B7="M"),MATCH(A1:A7,A1:A7,0),""),IF((B1:B7="M"),MATCH(A1:A7,A1:A7,0),""))>0,1))</code>

Note, this is an array formula, meaning you apply it to the cell with Ctrl+Shift+Enter, not just with Enter.

 

[T. Valko]

Hi all,
In the sample data below, I need to find the number of unique codes in column A coresponding to "M" in column B. Is there a simple formula I can use? I'm using Excel 2010 if that changes anything.
A B
1 1233 M
2 4562 M
3 1233 M
4 7625 S
5 7352 S
6 4562 M
7 2447 S

Thanks!

Try this array formula**:

=SUM(IF(FREQUENCY(IF(B2:B8="M",A2:A8),A2:A8),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Tom Urtis]

^
^
^

boop -- Go with Biff's formula...his is better than mine.

 

[boop]

Try this array formula**:

=SUM(IF(FREQUENCY(IF(B2:B8="M",A2:A8),A2:A8),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thanks guys, both your formulas worked on this sample data but it doesnt work when applied to my large file of 950 rows, I'll keep trying but I'm not sure where the error lies.

 

[T. Valko]

Thanks guys, both your formulas worked on this sample data but it doesnt work when applied to my large file of 950 rows, I'll keep trying but I'm not sure where the error lies.

My formula is based on column A containing numeric values (as is shown in your sample data).

 

[boop]

My data has numerical values in the first row representing staff numbers and letters in the second row representing staff locations so it should work the same as the sample data but its not working. There is a difference in that I use "ME*" instead of "M" as we use 2letter codes and I add the * because sometimes the person inputting the data adds a space after ME, does this affect the formula?

 

[T. Valko]

My data has numerical values in the first row representing staff numbers and letters in the second row representing staff locations so it should work the same as the sample data but its not working. There is a difference in that I use "ME*" instead of "M" as we use 2letter codes and I add the * because sometimes the person inputting the data adds a space after ME, does this affect the formula?

Yes, it does!

Can you use the criteria that column B starts with "M" ?

Array entered**:

=SUM(IF(FREQUENCY(IF(LEFT(B2:B8)="M",A2:A8),A2:A8),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[boop]

Yes, it does!

Can you use the criteria that column B starts with "M" ?

Array entered**:

=SUM(IF(FREQUENCY(IF(LEFT(B2:B8)="M",A2:A8),A2:A8),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

IT WORKED!!! Thank you so much!

Are you able to explain the logic behind not putting in "ME*"? just for future reference. I have previously used it in the below formulas and they've worked?

=COUNTIF(B:B,"ME*")
=SUMIF(B:B,"ME*",F:F)

Thank you so much for your help!

 

[T. Valko]

IT WORKED!!! Thank you so much!

Are you able to explain the logic behind not putting in "ME*"? just for future reference. I have previously used it in the below formulas and they've worked?

=COUNTIF(B:B,"ME*")
=SUMIF(B:B,"ME*",F:F)

Thank you so much for your help!

Both the COUNTIF and SUMIF functions support the use of wildcards. None of the functions in the other formula support wildcards so it was evaluating the * as the lteral asterisk character.