SUMPRODUCT using AND + OR

[WildWill]

Hi

I a table of data (A1:K50), I want to count all occurences where A1:50 = Not Empty AND (F1:50) = Not Empty OR G1:50 = Not Empty OR H1:50 = Not Empty.

I was thinking of using a SUMPRODUCT but not sure how I would indicate the above combinations in it?

 

[pgc01]

Hi

Your expression is not clear, since you did not use parentheses.

You posted

A And B Or C Or d

With the usual order of precedence this would mean

(A And B) Or C Or d

Is this correct?

 

[WildWill]

Hi

Your expression is not clear, since you did not use parentheses.

You posted

A And B Or C Or d

With the usual order of precedence this would mean

(A And B) Or C Or d

Is this correct?

Sorry about that. Yes, your interpretation is correct.

 

[pgc01]

OK. Try:

=SUMPRODUCT(0+((A1:A50<>"")*(F1:F50<>"")+(G1:G50 <>"")+(H1:H50<>"")>0))

 

[MartinL]

Just beat me to it

=SUMPRODUCT(($A$1:$A$5<>"")*($F$1:$F$5<>""))+(($G$1:$G$5<>"")+($H$1:$H$5<>""))

pgc01 why the start and end bits??

 

[pgc01]

pgc01 why the start and end bits??

Hi Martin

The start and end bits are necessary because you don't want to double count rows.

For ex., let's say that in row 5 both G5 and H5 are not empty. In that case with your expression you'll be adding 2 instead of 1. If also A5 and F5 are not empty you'll add 3.

Since in my case I compare the result of your expression with 0, I'll always add 1, no matter if the result of the expression is 1, 2 or 3.