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?

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" <[email protected]> wrote in message
news:[email protected]...
> 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?

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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?
>>
>>
>>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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?
> >>
> >>
> >>
>
>
>

Glad to help.


"DJS" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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?
>> >>
>> >>
>> >>
>>
>>
>>