Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use Date range function in Sum(if....) | Excel Discussion (Misc queries) | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
SUMIF with AND for range and criteria | Excel Worksheet Functions | |||
Sum range of values that meet date criteria | Excel Discussion (Misc queries) | |||
SUMIF with only one criteria cell within range | Excel Discussion (Misc queries) |