SUMIF

[GDawg]

Consider column N to contain random dates, MM/DD,YYYY from Jan through Dec - in a nonsequential order. The adjacent column O contains dollar values. How can i write a SUMIF(or other formula) that will total dollars for a given month. I can't figure a way to get SUMIF to accept a range of values (i.e., between) in lieu of a specific value. I would have the formula in multiple cells to total sales for Jan, Feb, etc.

Thanks,

GDawg

 

[Yogi Anand]

On 2002-04-25 10:33, GDawg wrote:
Consider column N to contain random dates, MM/DD,YYYY from Jan through Dec - in a nonsequential order. The adjacent column O contains dollar values. How can i write a SUMIF(or other formula) that will total dollars for a given month. I can't figure a way to get SUMIF to accept a range of values (i.e., between) in lieu of a specific value. I would have the formula in multiple cells to total sales for Jan, Feb, etc.

Thanks,

GDawg

Hi GDawg:
If the dates are in cells A2:A7, and amounts are in B2:B7, then
use the following formual:

=SUMPRODUCT((B2:B7)*(MONTH(A2:A7)=2))
where last argument 2 is the month.

HTH


_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature
This message was edited by Yogi Anand on 2003-01-19 13:41

 

[GDawg]

Thanks HTH - it worked perfectly

 

[Jay Petrulis]

Hi Gdawg,

HTH = Hope this helps.

Just a nice signoff wishing you good luck with the answer provided.

Yogi is the one you want to thank.

 

[Mark W.]

On 2002-04-25 10:33, GDawg wrote:
Consider column N to contain random dates, MM/DD,YYYY from Jan through Dec - in a nonsequential order. The adjacent column O contains dollar values. How can i write a SUMIF(or other formula) that will total dollars for a given month. I can't figure a way to get SUMIF to accept a range of values (i.e., between) in lieu of a specific value. I would have the formula in multiple cells to total sales for Jan, Feb, etc.

Thanks,

GDawg

Sum the dollars for Feb using...

{=SUM((MONTH(N1:N12)=2)*O1:O12)}

Note: This is an array formula which is entered using the Control+Shift+Enter key combination. For more on array formulas see the Excel Help Index topic for "About array formulas and how to enter them".