Using Countifs to count range of blank and non blank cell based on condition

[Tennisguuy]

I want to count the number of times where a group of cells are blank AND a group of another cells are not blank. In cells J13:L212 I want to count the number of cells that are blank AND in cells M13:O212 I want to count the number of time the cells are not blank if the value in cells I13:I212 is equal to A.

For example if there is a "A" in cell I13 and the cells are blank in J13:L13 but the cells in M13:013 are not blank the count should be 1 since there was a value say cell N13.

I tried this formula but it didn't work

IFERROR(COUNTIFS($I$13:$I$212,"A",$J$13:$L$212,"=",$M$13:$O$212,"<>"),0)

 

[jasonb75]

With countifs or similar functions all ranges must be equal size in the number of rows and columns. You have one range with a single column while the other ranges have 3 columns which is why your formula is not working.

blank in J13:L13 but the cells in M13:013 are not blank the count should be 1 since there was a value say cell N13.

Finding a formula that will work depends on how these 2 groups of cells should be compared, the devil is in the (very fine) details. Are you saying that all 3 cells in J13:L13 should be blank with 1 or more cells in M13:O13 not blank, or that J13 should be compared only to M13, K13 compared to N13 and L13 to O13?

 

[Tennisguuy]

Yes I am saying the all three cells in J13:L13 should be blank and anyone of the cells in N13:O13 are not blank and if that is true cell I13 must be an A but I13 does not need to be compared to anything

 

[jasonb75]

Try

=SUMPRODUCT(($I$13:$I$212="A")*($J$13:$J212="")*($K$13:$K212="")*($L$13:$L212="")*((($M$13:$M$212<>"")+($N$13:$N$212<>"")+($O$13:$O$212<>""))>0))

 

[Tennisguuy]

Thank Jason that worked perfectly. Really appreciate your help