Excel formulas
 

I need to calculate a sum using 2 different date ranges...example

Col A Col B
11/29/04 500
11/30/04 2
12/01/04 600
12/02/04 300

I cannot get SUMPRODUCT to work as it will only calc <=11/30/04 or
<=12/02/04 but not both. The sum I am looking for would be Nov04=502 and
Dec04=900.
Does this make sense or is it hopeless?

Try...

=SUMPRODUCT(--(A1:A4="12/1/2004"+0),--(A1:A4<="12/31/2004"+0),B1:B4)

or

=SUMPRODUCT(--(A1:A4=C1),--(A1:A4<=D1),B1:B4)

....where C1 contains the start date, and D1 contains the end date.

Hope this helps!

In article ,
"JN" wrote:

I need to calculate a sum using 2 different date ranges...example

Col A Col B
11/29/04 500
11/30/04 2
12/01/04 600
12/02/04 300

I cannot get SUMPRODUCT to work as it will only calc <=11/30/04 or
<=12/02/04 but not both. The sum I am looking for would be Nov04=502 and
Dec04=900.
Does this make sense or is it hopeless?

"Domenic" wrote in message
...
Try...

=SUMPRODUCT(--(A1:A4="12/1/2004"+0),--(A1:A4<="12/31/2004"+0),B1:B4)


or even

=SUMPRODUCT(--(A1:A4=--"2004-01-12"),--(A1:A4<=--"2004-01-31"),B1:B4)

with no nasty +0, and internationally standard dates :-)

In article ,
"Bob Phillips" wrote:

=SUMPRODUCT(--(A1:A4=--"2004-01-12"),--(A1:A4<=--"2004-01-31"),B1:B4)

with no nasty +0, and internationally standard dates :-)

Actually, for some reason, I find +0 more appealing to the eyes. Just a
personal preference. :)

And, yes, good point about using internationally standard dates. Should
eliminate any confusion.

Thank you so much!!! Could I beg you to help with another?

Col C Col E Col G
VA3 LAN 1
V4P V4P 2
V4P LNS 3

I need to get it to look at both Col C & Col E and, if the criteria matches,
total G (in the example, the sum would be 5). The criteria being V4P in Col
C and V4P OR LNS in Col E. I can get it to work for one or the other
SUMPRODUCT((C1:C3="V4P")*(E1:E3="???"),(G1:G3)) where ??? is either V4P or
LNS but I cannot get it to look for both and total it.


"Domenic" wrote:

In article ,
"Bob Phillips" wrote:

=SUMPRODUCT(--(A1:A4=--"2004-01-12"),--(A1:A4<=--"2004-01-31"),B1:B4)

with no nasty +0, and internationally standard dates :-)

Actually, for some reason, I find +0 more appealing to the eyes. Just a
personal preference. :)

And, yes, good point about using internationally standard dates. Should
eliminate any confusion.

Wonderful! Thanks so much for helping! Could I bother you for help one more
time?

Col C Col E Col G
VA1 XYZ 1
V4P V4P 2
V4P LNS 3
VA2 VA2 7

I am looking for Excel to look at Col C and Col E and, if the criteria is
correct, get totals from Col G. I used
SUMPRODUCT((C1:C4="V4P")*(E1:E4="???"),(G1:G4)) where ??? criteria is V4P or
LNS. This will work for each (V4P or LNS) but I need it to look at both.
The sum would 5 in the above example.

"Domenic" wrote:

Try...

=SUMPRODUCT(--(A1:A4="12/1/2004"+0),--(A1:A4<="12/31/2004"+0),B1:B4)

or

=SUMPRODUCT(--(A1:A4=C1),--(A1:A4<=D1),B1:B4)

....where C1 contains the start date, and D1 contains the end date.

Hope this helps!

In article ,
"JN" wrote:

I need to calculate a sum using 2 different date ranges...example

Col A Col B
11/29/04 500
11/30/04 2
12/01/04 600
12/02/04 300

I cannot get SUMPRODUCT to work as it will only calc <=11/30/04 or
<=12/02/04 but not both. The sum I am looking for would be Nov04=502 and
Dec04=900.
Does this make sense or is it hopeless?

How about:
=SUMPRODUCT((C1:C4="V4P")*(E1:E4={"xyz","v4p"})*(G 1:G4))



JN wrote:

Wonderful! Thanks so much for helping! Could I bother you for help one more
time?

Col C Col E Col G
VA1 XYZ 1
V4P V4P 2
V4P LNS 3
VA2 VA2 7

I am looking for Excel to look at Col C and Col E and, if the criteria is
correct, get totals from Col G. I used
SUMPRODUCT((C1:C4="V4P")*(E1:E4="???"),(G1:G4)) where ??? criteria is V4P or
LNS. This will work for each (V4P or LNS) but I need it to look at both.
The sum would 5 in the above example.

"Domenic" wrote:

Try...

=SUMPRODUCT(--(A1:A4="12/1/2004"+0),--(A1:A4<="12/31/2004"+0),B1:B4)

or

=SUMPRODUCT(--(A1:A4=C1),--(A1:A4<=D1),B1:B4)

....where C1 contains the start date, and D1 contains the end date.

Hope this helps!

In article ,
"JN" wrote:

I need to calculate a sum using 2 different date ranges...example

Col A Col B
11/29/04 500
11/30/04 2
12/01/04 600
12/02/04 300

I cannot get SUMPRODUCT to work as it will only calc <=11/30/04 or
<=12/02/04 but not both. The sum I am looking for would be Nov04=502 and
Dec04=900.
Does this make sense or is it hopeless?


--

Dave Peterson

You have saved my life! Thanks!!!!

"Dave Peterson" wrote:

How about:
=SUMPRODUCT((C1:C4="V4P")*(E1:E4={"xyz","v4p"})*(G 1:G4))



JN wrote:

Wonderful! Thanks so much for helping! Could I bother you for help one more
time?

Col C Col E Col G
VA1 XYZ 1
V4P V4P 2
V4P LNS 3
VA2 VA2 7

I am looking for Excel to look at Col C and Col E and, if the criteria is
correct, get totals from Col G. I used
SUMPRODUCT((C1:C4="V4P")*(E1:E4="???"),(G1:G4)) where ??? criteria is V4P or
LNS. This will work for each (V4P or LNS) but I need it to look at both.
The sum would 5 in the above example.

"Domenic" wrote:

Try...

=SUMPRODUCT(--(A1:A4="12/1/2004"+0),--(A1:A4<="12/31/2004"+0),B1:B4)

or

=SUMPRODUCT(--(A1:A4=C1),--(A1:A4<=D1),B1:B4)

....where C1 contains the start date, and D1 contains the end date.

Hope this helps!

In article ,
"JN" wrote:

I need to calculate a sum using 2 different date ranges...example

Col A Col B
11/29/04 500
11/30/04 2
12/01/04 600
12/02/04 300

I cannot get SUMPRODUCT to work as it will only calc <=11/30/04 or
<=12/02/04 but not both. The sum I am looking for would be Nov04=502 and
Dec04=900.
Does this make sense or is it hopeless?


--

Dave Peterson