Count conditional cells with CountIf

[ace3g]

If I have a condition =COUNTIF($M$2:$R$27,B2)>=1 that turns certain cells green, how can I count those cells in row that are green? Range that turns green is: =B2:G39 (based on number inputted in M2:R27)-- First row I want to count is B2:G2.

I think I almost figured it out: =SUM(COUNTIF(B2:G2,$M$2:$R$27)) but I only want it to count the first time a number in B2:G2 matches numbers from M2:R27, no duplicates.

 

[RoryA]

Welcome to the forum.

Please just take a minute to read the forum rules on cross-posting and make sure to follow them in future. Thanks.

Cross-posted here.

 

[ace3g]

My apologies, I will definitely remember this rule in the future.

 

[ace3g]

Since I am new, how do you post/upload the excel sheet?

 

[RoryA]

You can't upload the actual file to this site (though you can put it elsewhere and link to it here) but see the links in my signature for details of the forum add-in for posting tables of your sheet here.

 

[ace3g]

Below is a screen cap of my sheet if that helps. The total for B2:G2 should be 6 not 18 and B3:G3 should be 4. Currently looking at numbers entered in M2:R4. Since there are multiple 1s and 3s it keeps counting those.

 

[RoryA]

Try this:

=SUM(--(COUNTIF($M$2:$R$27,$B2:$G2)>0))

which needs to be array-entered with Ctrl+Shift+Enter, or:

=SUMPODUCT(--(COUNTIF($M$2:$R$27,$B2:$G2)>0))

which can be normally entered.

 

[ace3g]

Wow thank you very much, this worked!!!

You can mark this as being solved and I'll forward over to the cross post.

 

[RoryA]

We don't actually mark threads as solved here, but I'm glad to help.