Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Two Conditions
Hi All,
This isn't working. =IF(AND($B$5:$B$5939721,$B$5:$B$59<39813),SUMIF($ D$5:$D$59,$C69,$F $5:$F$59)) entered with Ctrl-Shft-Enter. I'm trying to the a total amount in the range F5 to F59 if the date in B5 to B59 (corresponding the number in F5 to F59 is between 39721 and 39813 and the text in D5 to D59 matches C69. Any help would be appreciated. Thanks, Michele |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Two Conditions
Try this:
=SUMPRODUCT(--(B5:B59=39721),--(B5:B59<=39813),--(D5:D59=C69),F5:F59) Basically, the first terms (with the "--") return a '1' if true and '0' if false, so you end up adding the sum only if all the other terms are true. If you have Excel 2007, there is a "=SUMIFS" function that can do much the same thing. hth "mjones" wrote: Hi All, This isn't working. =IF(AND($B$5:$B$5939721,$B$5:$B$59<39813),SUMIF($ D$5:$D$59,$C69,$F $5:$F$59)) entered with Ctrl-Shft-Enter. I'm trying to the a total amount in the range F5 to F59 if the date in B5 to B59 (corresponding the number in F5 to F59 is between 39721 and 39813 and the text in D5 to D59 matches C69. Any help would be appreciated. Thanks, Michele |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Two Conditions
See my post below solving this. Similar but one has to use Date(2008,9,30)
idea. "pdberger" wrote: Try this: =SUMPRODUCT(--(B5:B59=39721),--(B5:B59<=39813),--(D5:D59=C69),F5:F59) Basically, the first terms (with the "--") return a '1' if true and '0' if false, so you end up adding the sum only if all the other terms are true. If you have Excel 2007, there is a "=SUMIFS" function that can do much the same thing. hth "mjones" wrote: Hi All, This isn't working. =IF(AND($B$5:$B$5939721,$B$5:$B$59<39813),SUMIF($ D$5:$D$59,$C69,$F $5:$F$59)) entered with Ctrl-Shft-Enter. I'm trying to the a total amount in the range F5 to F59 if the date in B5 to B59 (corresponding the number in F5 to F59 is between 39721 and 39813 and the text in D5 to D59 matches C69. Any help would be appreciated. Thanks, Michele |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif with conditions | Excel Worksheet Functions | |||
Sumif with n conditions | Excel Worksheet Functions | |||
SUMIF with 2 conditions | Excel Worksheet Functions | |||
sumif more conditions | Excel Worksheet Functions | |||
SUMIF with two conditions | Excel Discussion (Misc queries) |