COUNTIF question

[Pinaceous]

Hi All,

I have the formula

=COUNTIF(C4:P29,"POP")

that counts all values of the POP. Now on the spread sheet I use the words POP with a number preceding it to indicate another variable.

For example, I write in that range 1POP in one cell, and another then 2POP and so on until whenever. So, is there any way to count these values, the #POP's as a separate entry in the COUNTIF formula?

For example,

=COUNTIF(C4:P29,"(#)POP")

??

Thank you!
Pinaceous

 

[Eric W]

=COUNTIF(C4:P29,"*POP")

will count anything that ends with POP.

 

[Pinaceous]

Hi Eric,

Thank you for posting your formula!

Pinaceous

 

[Pinaceous]

=COUNTIF(C4:P29,"*POP")

will count anything that ends with POP.

Do you know how I can adjust this formula to differentiate the (#)POP entries with the POP entries as separate entities?

 

[Nishant94]

=countifs(c4:p29,"*pop",c4:p29,"<>#pop")

 

[Eric W]

=COUNTIF(C4:P29,"POP")

will count just the entries that are exactly "POP",

=COUNTIF(C4:P29,"?POP")

will count just the entries that have 1 character + "POP". Since # is not a valid wildcard for SUMIF, there's not a good way to differentiate between digits and letters as the first character. If that's an issue, we can get fancier with SUMPRODUCT.

 

[Pinaceous]

Hello Eric,

Thanks for posting:

=COUNTIF(C4:P29,"?POP")

will count just the entries that have 1 character + "POP". Since # is not a valid wildcard for SUMIF, there's not a good way to differentiate between digits and letters as the first character. If that's an issue, we can get fancier with SUMPRODUCT.

How would we take count

##POP

with a number that goes that counts the ones digit and the tens digits?


For example, counting 11POP to 99POP?


I was thinking:

=COUNTIF(C4:P29,"?LWOP")+COUNTIF(C4:P29,"??LWOP")

But this does not work.

Do you have any suggestions??

Thanks again!

Paul

 

[Eric W]

I'm not entirely clear on your question, but

=SUMPRODUCT(--ISNUMBER(LEFT(C4:P29,2)+0),--(MID(C4:P29,3,99)="POP"))

will sum up the cells starting with 2 digits followed by POP, and

=SUMPRODUCT(--ISNUMBER(LEFT(C4:P29,1)+0),--(MID(C4:P29,2,99)="POP"))

will sum up the cells starting with 1 digit followed by POP.

 

[Pinaceous]

=SUMPRODUCT(--ISNUMBER(LEFT(C4:P29,2)+0),--(MID(C4:P29,3,99)="POP"))

will sum up the cells starting with 2 digits followed by POP, and

=SUMPRODUCT(--ISNUMBER(LEFT(C4:P29,1)+0),--(MID(C4:P29,2,99)="POP"))

will sum up the cells starting with 1 digit followed by POP.

Hi Eric!

Thanks for posting those formulas.

I have an additional question for you.

Could you rearrange your formulas, written in reverse order for the following examples?


For example, could you change

=SUMPRODUCT(--ISNUMBER(LEFT(C4:P29,2)+0),--(MID(C4:P29,3,99)="POP"))

to sum up the cells starting with POP followed by 2 digits.

Likewise, could you change

=SUMPRODUCT(--ISNUMBER(LEFT(C4:P29,1)+0),--(MID(C4:P29,2,99)="POP"))

to sum up the cells starting with POP followed by 1 digit

I'd thought if I change the LEFT to RIGHT it would work, but it doesn't.

Thanks for your help!
Paul

 

[Eric W]

This version counts values that start with "POP", and end with a number of any length:

=SUMPRODUCT(--(LEFT(C4:P29,3)="POP"),--ISNUMBER(MID(C4:P29,4,9)+0))

If you want to distinguish between 1-digit and 2-digit numbers, you can add a check for the length:

=SUMPRODUCT(--(LEFT(C4:P29,3)="POP"),--ISNUMBER(MID(C4:P29,4,9)+0),--(LEN(C4:P29)=4))

Change the number in red to be the total length of the value, POP = 3 digits, plus 1 for a 1-digit number.