too long sumif help

[Guest]

Hi
Could I create short formula

=SUMIF(A1:E1;G$4;A2:E2)+SUMIF(A3:E3;G$4;A4:E4)+SUMIF(A5:E5;G$4;A6:E6)+SUMIF(A7:E7;G$4;A8:E8)+SUMIF(A9:E9;G$4;A10:E10)+SUMIF(A11:E11;G$4;A12:E12)+SUMIF(A13:E13;G$4;A14:E14)..............+SUMIF(A201:E201;G$4;A202:E202)

 

[Bob Phillips]

=SUMPRODUCT((MOD(ROW(A1:E201),2)=1)*(A1:E201="a"),A2:E202)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

Hi
Could I create short formula

=SUMIF(A1:E1;G$4;A2:E2)+SUMIF(A3:E3;G$4;A4:E4)+SUMIF(A5:E5;G$4;A6:E6)+SUMIF(
A7:E7;G$4;A8:E8)+SUMIF(A9:E9;G$4;A10:E10)+SUMIF(A11:E11;G$4;A12:E12)+SUMIF(A
13:E13;G$4;A14:E14)..............+SUMIF(A201:E201;G$4;A202:E202)

 

[Domenic]

Try...

=SUMPRODUCT(--(A1:E201=G4),A2:E202)

Hope this helps!

 

[Bob Phillips]

The "a" in my solution should be replaced with G4, that is what I tested
with

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Like the idea Domenic.

You could do it with SUMIF no need for SUMPRODUCT, and it could return a
result from the wrong rows if say A14=G4 and A15 is a value.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Domenic]

You could do it with SUMIF no need for SUMPRODUCT...

Definitely! I should have used SUMIF...

...and it could return a
result from the wrong rows if say A14=G4 and A15 is a value.

True, but I don't think that would be the case. Of course, I could be
wrong...

 

[Guest]

Hi

Domenic formula SUMPRODUCT is perfect. But Bob formula not working.I hope
this is my mistake.The MOD not understandability for me.Thanks.

"Domenic" kirjutas:

 

[Domenic]

In that case, use SUMIF instead...

=SUMIF(A1:E201,"A",A2:E202)

....it's more efficient.

Hope this helps!

 

[Guest]

Sumif working. Now its so easy.Thanks.

"Domenic" kirjutas: