Sumif with two criteria including a date range

[Guest]

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",D21228)

or the essentials/luxuries criteria:

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

but how do I do both together?

Thank you!

 

[Bob Phillips]

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


--
HTH

Bob Phillips

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

 

[Guest]

Hi,

Try something like this:

=SUMPRODUCT((A2:A1228<=DATE(2007,2,1)*(A2:A1228>=DATE(2007,1,12)*(G2:G1228="e"),D21228)

HTH
Jean-Guy

 

[Guest]

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

 

[David Biddulph]

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