Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif using "AND" and "Date"
I wish to find the total of amounts in col H, relating to the date in col B
is between 01 Apr and 30 Apr. I wrote this formula, but it keeps evaluating to 0 [zero]. Why? I suppose I am not using the nested "AND" function correctly: =SUMIF(B3:B141,AND(""&DATE(2009,3,31),"<"&DATE(20 09,5,1)),H3:H141) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif using "AND" and "Date"
You can only use SUMIF for a single condition. Try this instead:
=SUMPRODUCT((B3:B141DATE(2009,3,31))*(B3:B141<DAT E (2009,5,1)),H3:H141) or this: =SUMPRODUCT(--(TEXT(B3:B141,"mmm yy")="Apr 09"),H3:H141) Hope this helps. Pete On Oct 26, 8:58*am, Excel_Kiwi wrote: I wish to find the total of amounts in col H, relating to the date in col B is between 01 Apr and 30 Apr. *I wrote this formula, but it keeps evaluating to 0 [zero]. Why? I suppose I am not using the nested "AND" function correctly: =SUMIF(B3:B141,AND(""&DATE(2009,3,31),"<"&DATE(20 09,5,1)),H3:H141) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif using "AND" and "Date"
Try the formula
=SUMIF(B3:B141,""&DATE(2009,3,31),H3:H141)- SUMIF(B3:B141,"=" &DATE(2009,5,1),H3:H141) OR =SUMPRODUCT(--(MONTH(B3:B141)=4),H3:H141) If this post helps click Yes --------------- Jacob Skaria "Excel_Kiwi" wrote: I wish to find the total of amounts in col H, relating to the date in col B is between 01 Apr and 30 Apr. I wrote this formula, but it keeps evaluating to 0 [zero]. Why? I suppose I am not using the nested "AND" function correctly: =SUMIF(B3:B141,AND(""&DATE(2009,3,31),"<"&DATE(20 09,5,1)),H3:H141) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |