sumproduct of columns

[Guest]

Hello~
For each row in my report I need to add columns ES:EZ and determine if the
val is equal to zero, then return a count of all rows where columns ES:EZ
added up to 0 .
Can I use Sumproduct for this or is there another way?

 

[Dave]

DJS

How about this

Add a helper column in Column FA with the following fomula and copy down to
the end of your data

=SUM(ES2:EZ2)

Use this formula to count the zeros.

=COUNTIF(FA2:FA200,"=0")

Adjust ranges to meet your data requirements.

Dave

 

[Guest]

Thanks Dave, but i can't modify that sheet.
All of my functions are in my main sheet and reference other sheets or tabs.
Any other way to do this from one cell in my main sheet?

 

[Dave]

DJS

Try this

=SUMPRODUCT(--((ES2:ES5)=0),--((ET2:ET5)=0),--((EU2:EU5)=0),--((EV2:EV5)=0),--((EW2:EW5)=0),--((EX2:EX5)=0),--((EY2:EY5)=0),--((EZ2:EZ5)=0))

Adjust ranges to suit. Note all ranges must be the same size.

Dave

 

[Guest]

Thanks Dave, that's the ticket!
I thought I tried that simple function, but didn't seem to get the same
results.

 

[Dave]

Glad to help.

Thanks Dave, that's the ticket!
I thought I tried that simple function, but didn't seem to get the same
results.