How to COUNT IF NOT

[Vbort44]

I've calculated the count of each of my products in column A (Products A, B, C and D) if the product has an associated score in column B; however, I still need to calculate the count of products E, F, G, H, I, J and K if they have scores (grouped together as "other").

Here's and example of the formula that I've used to calculate the count of product A if column B has a score: {=COUNT(IF($A$2:$A$60000="Product A",IF($B$2:$B$60000<>"",$B$2:$B$60000)))}

Basically I'm struggling with combining IF not statements. So, count if column A does not have products A, B, C, D and has an associated score in column B.

I hope this makes sense...

[NBVC]

Try:

=COUNT(IF(ISNA(MATCH($A$2:$A$6000,{"Product A","Product B","Product C","Product D"},0)),IF($B$2:$B$6000<>"",1)))

confirmed with CTRL+SHIFT+ENTER

[Ron Coderre]

Try this:

Please Login or Register  to view this content.

Is that something you can work with?

[Vbort44]

Okay, great - both make sense. But what if I want to calculate the average score of "other" in column B

So, average if column A does not have products A, B, C, D and has an associated score in column B.

I treid this and it didn't work:

Please Login or Register  to view this content.

[NBVC]

Try:

=AVERAGE(IF(ISNA(MATCH($A$2:$A$6000,{"A","B","C","D"},0)),IF($B$2:$B$6000<>"",$B$2:$B$6000)))

confirmed with CTRL+SHIFT+ENTER

[Vbort44]

Try:

=AVERAGE(IF(ISNA(MATCH($A$2:$A$6000,{"A","B","C","D"},0)),IF($B$2:$B$6000<>"",$B$2:$B$6000)))

confirmed with CTRL+SHIFT+ENTER

PERFECT! Thanks!

[Vbort44]

Try:

=COUNT(IF(ISNA(MATCH($A$2:$A$6000,{"Product A","Product B","Product C","Product D"},0)),IF($B$2:$B$6000<>"",1)))

confirmed with CTRL+SHIFT+ENTER

Now it's starting to get difficult. What if I had two statements that had to be true to calculate average in column B. Let's say that product b had to be found in column a and I had a column C with either "true" or blank in the column. For example:

Count if column A matches product B, has an associated score in column B AND matches "true" in column C.

[NBVC]

You can just add another nested IF()

e.g.

=COUNT(IF($A$2:$A$6000="Product B",IF($C$2:$C$6000=TRUE,IF($B$2:$B$6000<>"",1))))

[Vbort44]

You can just add another nested IF()

e.g.

=COUNT(IF($A$2:$A$6000="Product B",IF($C$2:$C$6000=TRUE,IF($B$2:$B$6000<>"",1))))

Ah, but of course, thanks!