sumif and array formulas
 

Bruce
So close - try swapping the IF and the SUM:

{=SUM(IF(CT2:CW300)=0,SUM(CW2:DH300),0)}

cheers
Simon

"Bruce" wrote:

In the following formaula i am essentually summing a range if another range
in the same row = zero

=IF(SUM(CT2:CW2)=0,SUM(CW2:DH2),0)

My worksheet fill this down to row 300 and I total this at the the top,
however I am not really after the detail of each row but ratherjust the total
as I am going to roll the formula and the detail will add too many columns to
my sheet.

How do I modify the above as an array formula so that it inspect and total
for rows 2 to 300.

I have this but its does not work;

{=IF(SUM(CT2:CW300)=0,SUM(CW2:DH300),0)}

Bruce

Thanks Simon,

I assume that I need to adjust my bracketing as such

{=SUM(IF(CT2:CW300=0,SUM(CW2:DH300)),0)}

However the result it gives me is far greater than a simple sum(CW2:DH300)

Any ideas why?

Bruce

"Simon Murphy" wrote:

Bruce
So close - try swapping the IF and the SUM:

{=SUM(IF(CT2:CW300)=0,SUM(CW2:DH300),0)}

cheers
Simon

"Bruce" wrote:

In the following formaula i am essentually summing a range if another range
in the same row = zero

=IF(SUM(CT2:CW2)=0,SUM(CW2:DH2),0)

My worksheet fill this down to row 300 and I total this at the the top,
however I am not really after the detail of each row but ratherjust the total
as I am going to roll the formula and the detail will add too many columns to
my sheet.

How do I modify the above as an array formula so that it inspect and total
for rows 2 to 300.

I have this but its does not work;

{=IF(SUM(CT2:CW300)=0,SUM(CW2:DH300),0)}

Bruce

Bruce wrote...
In the following formaula i am essentually summing a range if another
range
in the same row = zero

=IF(SUM(CT2:CW2)=0,SUM(CW2:DH2),0)
....
How do I modify the above as an array formula so that it inspect and
total
for rows 2 to 300.
....

You want the row-wise sums of CT:CW and CW:DH. Use MMULT.

=SUMPRODUCT(--(MMULT(CT2:CW300,{1;1;1;1})=0),
MMULT(CW2:DH2,{1;1;1;1;1;1;1;1;1;1;1;1}))

Harlin,

Close to what Im after but not quite. To elaborate your interpretation;

I want the row-wise sum of CW2:CH300 if the corresponding row-wise sum of
CT2:CW2 = 0.

I know the correct number it should return but I cant get it to work from
these suggestions.

Bruce


"Harlan Grove" wrote:

Bruce wrote...
In the following formaula i am essentually summing a range if another
range
in the same row = zero

=IF(SUM(CT2:CW2)=0,SUM(CW2:DH2),0)
....
How do I modify the above as an array formula so that it inspect and
total
for rows 2 to 300.
....

You want the row-wise sums of CT:CW and CW:DH. Use MMULT.

=SUMPRODUCT(--(MMULT(CT2:CW300,{1;1;1;1})=0),
MMULT(CW2:DH2,{1;1;1;1;1;1;1;1;1;1;1;1}))

Bruce wrote...
Harlin,
....

First, if you can't bother to spell names correctly, don't bother
including salutations.

I want the row-wise sum of CW2:CH300 if the corresponding row-wise
sum of
CT2:CW2 = 0.

You showed CW2:DH300 before, not CH2:CW300. I'll assume the original
was correct. I screwed up my second range reference. Try

=SUMPRODUCT(--(MMULT(CT2:CW300,{1;1;1;1})=0),
MMULT(CW2:DH300,{1;1;1;1;1;1;1;1;1;1;1;1}))

If you actually do want to sum CH:CW, then use

=SUMPRODUCT(--(MMULT(CT2:CW300,{1;1;1;1})=0),
MMULT(CH2:CW300,{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}) )