sumif using "AND" and "Date"

[Excel_Kiwi]

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(2009,5,1)),H3:H141)

 

[Pete_UK]

You can only use SUMIF for a single condition. Try this instead:

=SUMPRODUCT((B3:B141>DATE(2009,3,31))*(B3:B141<DATE
(2009,5,1)),H3:H141)

or this:

=SUMPRODUCT(--(TEXT(B3:B141,"mmm yy")="Apr 09"),H3:H141)

Hope this helps.

Pete

 

[Jacob Skaria]

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