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)

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)

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)