too long sumif help
 

Hi
Could I create short formula

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

too long sumif help
 

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Jaan" wrote in message
...
Hi
Could I create short formula


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

too long sumif help
 

Try...

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

Hope this helps!

In article ,
"Jaan" wrote:

Hi
Could I create short formula

=SUMIF(A1:E1;G$4;A2:E2)+SUMIF(A3:E3;G$4;A4:E4)+SUM IF(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:E
13;G$4;A14:E14)..............+SUMIF(A201:E201;G$4; A202:E202)

too long sumif help
 

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" wrote in message
...
=SUMPRODUCT((MOD(ROW(A1:E201),2)=1)*(A1:E201="a"), A2:E202)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Jaan" wrote in message
...
Hi
Could I create short formula



=SUMIF(A1:E1;G$4;A2:E2)+SUMIF(A3:E3;G$4;A4:E4)+SUM IF(A5:E5;G$4;A6:E6)+SUMIF(

A7:E7;G$4;A8:E8)+SUMIF(A9:E9;G$4;A10:E10)+SUMIF(A1 1:E11;G$4;A12:E12)+SUMIF(A
13:E13;G$4;A14:E14)..............+SUMIF(A201:E201; G$4;A202:E202)

too long sumif help
 

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

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

Hope this helps!

In article ,
"Jaan" wrote:

Hi
Could I create short formula


=SUMIF(A1:E1;G$4;A2:E2)+SUMIF(A3:E3;G$4;A4:E4)+SUM IF(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
:E
13;G$4;A14:E14)..............+SUMIF(A201:E201;G$4; A202:E202)

too long sumif help
 

In article ,
"Bob Phillips" wrote:

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... :)

too long sumif help
 

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:

In article ,
"Bob Phillips" wrote:

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... :)

too long sumif help
 

In that case, use SUMIF instead...

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

....it's more efficient.

Hope this helps!

In article ,
"Jaan" wrote:

Hi

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

too long sumif help
 

Sumif working. Now its so easy.Thanks.

"Domenic" kirjutas:

In that case, use SUMIF instead...

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

....it's more efficient.

Hope this helps!

In article ,
"Jaan" wrote:

Hi

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