sumproduct of columns

D

DJS

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?
 
D

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
 
D

DJS

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?
 
D

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
 
D

DJS

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