sumproduct of columns
 

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?

sumproduct of columns
 

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

"DJS" wrote in message
...
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?

sumproduct of columns
 

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" wrote:

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

"DJS" wrote in message
...
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?

sumproduct of columns
 

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

"DJS" wrote in message
...
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" wrote:

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

"DJS" wrote in message
...
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?

sumproduct of columns
 

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


"Dave" wrote:

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

"DJS" wrote in message
...
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" wrote:

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

"DJS" wrote in message
...
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?

sumproduct of columns
 

Glad to help.


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


"Dave" wrote:

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

"DJS" wrote in message
...
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" wrote:

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

"DJS" wrote in message
...
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?