software

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)

=SUMPRODUCT((MOD(ROW(A1:E201),2)=1)*(A1:E201=quot;aquot;), A2:E202)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Jaanquot; gt; wrote in message
...
gt; Hi
gt; Could I create short formula
gt;
gt;
=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)
Try...

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

Hope this helps!

In article gt;,
quot;Jaanquot; gt; wrote:

gt; Hi
gt; Could I create short formula
gt;
gt; =SUMIF(A1:E1;G$4;A2:E2) SUMIF(A3:E3;G$4;A4:E4) SUM IF(A5:E5;G$4;A6:E6) SUMIF(A7
gt; :E7;G$4;A8:E8) SUMIF(A9:E9;G$4;A10:E10) SUMIF(A11: E11;G$4;A12:E12) SUMIF(A13:E
gt; 13;G$4;A14:E14).............. SUMIF(A201:E201;G$4; A202:E202)

The quot;aquot; 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)

quot;Bob Phillipsquot; gt; wrote in message
...
gt; =SUMPRODUCT((MOD(ROW(A1:E201),2)=1)*(A1:E201=quot;aquot;), A2:E202)
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;Jaanquot; gt; wrote in message
gt; ...
gt; gt; Hi
gt; gt; Could I create short formula
gt; gt;
gt; gt;
gt;
=SUMIF(A1:E1;G$4;A2:E2) SUMIF(A3:E3;G$4;A4:E4) SUM IF(A5:E5;G$4;A6:E6) SUMIF(
gt;
A7:E7;G$4;A8:E8) SUMIF(A9:E9;G$4;A10:E10) SUMIF(A1 1:E11;G$4;A12:E12) SUMIF(A
gt; 13:E13;G$4;A14:E14).............. SUMIF(A201:E201; G$4;A202:E202)
gt;
gt;
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)

quot;Domenicquot; gt; wrote in message
...
gt; Try...
gt;
gt; =SUMPRODUCT(--(A1:E201=G4),A2:E202)
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;Jaanquot; gt; wrote:
gt;
gt; gt; Hi
gt; gt; Could I create short formula
gt; gt;
gt; gt;
=SUMIF(A1:E1;G$4;A2:E2) SUMIF(A3:E3;G$4;A4:E4) SUM IF(A5:E5;G$4;A6:E6) SUMIF(
A7
gt; gt;
:E7;G$4;A8:E8) SUMIF(A9:E9;G$4;A10:E10) SUMIF(A11: E11;G$4;A12:E12) SUMIF(A13
:E
gt; gt; 13;G$4;A14:E14).............. SUMIF(A201:E201;G$4; A202:E202)
In article gt;,
quot;Bob Phillipsquot; gt; wrote:

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

Definitely! I should have used SUMIF...

gt; ...and it could return a
gt; 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...

Hi

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

quot;Domenicquot; kirjutas:

gt; In article gt;,
gt; quot;Bob Phillipsquot; gt; wrote:
gt;
gt; gt; You could do it with SUMIF no need for SUMPRODUCT...
gt;
gt; Definitely! I should have used SUMIF...
gt;
gt; gt; ...and it could return a
gt; gt; result from the wrong rows if say A14=G4 and A15 is a value.
gt;
gt; True, but I don't think that would be the case. Of course, I could be
gt; wrong...
gt;

In that case, use SUMIF instead...

=SUMIF(A1:E201,quot;Aquot;,A2:E202)

....it's more efficient.

Hope this helps!

In article gt;,
quot;Jaanquot; gt; wrote:

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

Sumif working. Now its so easy.Thanks.

quot;Domenicquot; kirjutas:

gt; In that case, use SUMIF instead...
gt;
gt; =SUMIF(A1:E201,quot;Aquot;,A2:E202)
gt;
gt; ....it's more efficient.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;Jaanquot; gt; wrote:
gt;
gt; gt; Hi
gt; gt;
gt; gt; Domenic formula SUMPRODUCT is perfect. But Bob formula not working.I hope
gt; gt; this is my mistake.The MOD not understandability for me.Thanks.
gt;