Sumif with two criteria including a date range
 

I have a spreadsheet of my personal spending and I want to sum up how much I
have spent on a) luxuries and b) essentials ("l" and "e") in a particular
date range. I can get it to work if I ignore either the date range criteria:

=SUMIF(G2:G1228, "e",D2:D1228)

or the essentials/luxuries criteria:

=SUMIF(A2:A2222,"<="&DATE(2007,2,11),D2:D2222)-SUMIF(A2:A2222,"<"&DATE(2007,1,12),D2:D2222)

but how do I do both together?

Thank you!

Sumif with two criteria including a date range
 

=SUMPRODUCT(--(A2:A2222,=--"2007-01-12"),--(A2:A2222,<=--"2007-02-11"),--(G
2:G2222="e"),D2:D2222)


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Ladyofthewhitecity" wrote in
message ...
I have a spreadsheet of my personal spending and I want to sum up how much
I
have spent on a) luxuries and b) essentials ("l" and "e") in a particular
date range. I can get it to work if I ignore either the date range
criteria:

=SUMIF(G2:G1228, "e",D2:D1228)

or the essentials/luxuries criteria:


=SUMIF(A2:A2222,"<="&DATE(2007,2,11),D2:D2222)-SUMIF(A2:A2222,"<"&DATE(2007,
1,12),D2:D2222)

but how do I do both together?

Thank you!

Sumif with two criteria including a date range
 

Hi,

Try something like this:

=SUMPRODUCT((A2:A1228<=DATE(2007,2,1)*(A2:A1228=D ATE(2007,1,12)*(G2:G1228="e"),D2:D1228)

HTH
Jean-Guy

"Ladyofthewhitecity" wrote:

I have a spreadsheet of my personal spending and I want to sum up how much I
have spent on a) luxuries and b) essentials ("l" and "e") in a particular
date range. I can get it to work if I ignore either the date range criteria:

=SUMIF(G2:G1228, "e",D2:D1228)

or the essentials/luxuries criteria:

=SUMIF(A2:A2222,"<="&DATE(2007,2,11),D2:D2222)-SUMIF(A2:A2222,"<"&DATE(2007,1,12),D2:D2222)

but how do I do both together?

Thank you!

Sumif with two criteria including a date range
 

Hi,

Unfortunately neither of those forumlas worked - "this formula contains an
error".
Any reasons why?

Column A is a date column (format eg 20-Jan-2007, and they are formatted as
date cells)
Column D is a list of prices
Column G tells me whether its an essential ("e") or a luxury ("l")

So far I have data in rows 2 to 71, but I am constantly adding to this hence
the range 2:2222.

I am using excel 2000 if that helps!

Thanks

Hazel


"pinmaster" wrote:

Hi,

Try something like this:

=SUMPRODUCT((A2:A1228<=DATE(2007,2,1)*(A2:A1228=D ATE(2007,1,12)*(G2:G1228="e"),D2:D1228)

HTH
Jean-Guy

"Ladyofthewhitecity" wrote:

I have a spreadsheet of my personal spending and I want to sum up how much I
have spent on a) luxuries and b) essentials ("l" and "e") in a particular
date range. I can get it to work if I ignore either the date range criteria:

=SUMIF(G2:G1228, "e",D2:D1228)

or the essentials/luxuries criteria:

=SUMIF(A2:A2222,"<="&DATE(2007,2,11),D2:D2222)-SUMIF(A2:A2222,"<"&DATE(2007,1,12),D2:D2222)

but how do I do both together?

Thank you!

Sumif with two criteria including a date range
 

The most recent formula in the thread seems to have unmatched parentheses,
so it's probably sensible to resolve that problem.
--
David Biddulph

"Ladyofthewhitecity" wrote in
message ...
Hi,

Unfortunately neither of those forumlas worked - "this formula contains an
error".
Any reasons why?

Column A is a date column (format eg 20-Jan-2007, and they are formatted
as
date cells)
Column D is a list of prices
Column G tells me whether its an essential ("e") or a luxury ("l")

So far I have data in rows 2 to 71, but I am constantly adding to this
hence
the range 2:2222.

I am using excel 2000 if that helps!

Thanks

Hazel


"pinmaster" wrote:

Hi,

Try something like this:

=SUMPRODUCT((A2:A1228<=DATE(2007,2,1)*(A2:A1228=D ATE(2007,1,12)*(G2:G1228="e"),D2:D1228)

HTH
Jean-Guy

"Ladyofthewhitecity" wrote:

I have a spreadsheet of my personal spending and I want to sum up how
much I
have spent on a) luxuries and b) essentials ("l" and "e") in a
particular
date range. I can get it to work if I ignore either the date range
criteria:

=SUMIF(G2:G1228, "e",D2:D1228)

or the essentials/luxuries criteria:

=SUMIF(A2:A2222,"<="&DATE(2007,2,11),D2:D2222)-SUMIF(A2:A2222,"<"&DATE(2007,1,12),D2:D2222)

but how do I do both together?

Thank you!