Excel - Using SumIf with a date range 1

[davedave24]

Hi. I have a simple spreadsheet that I need to total up values per month.

Column A contains dates (shortdate eg 24/02/2012), Column B contains values (currency) related to the date in A.

How can I use SumIf to total up each month? Currently I have:

=SUMIF($A3:$A380,MONTH=2,$B3:$B52)

This returns 0; I've also tried

MONTH(2)

. How do I properly reference the month?

Cheers.

 

[SkipVought]

hi,

Does your date range have a Named Range of MONTH?

It is not a good practice to use reserver words, like MONTH as a named range!

ALSO, your ranges MUST be identical in size. You have column A as rows 3:380 and column B as rows 3:52. THAT WON'T WORK!

I'm guessing, though, that you are confused about MONTH.

Also, MONTH in the range won't work with SUMIF()

=SUMPRODUCT((MONTH($A3:$A380)=2)*($B3:$B380))

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, one shortcoming of using MONTH as a value is, that if your data includes dates from more than one year, you may not get the desired result.

More often, I use a date range, like...
[tt]
=SUMPRODUCT(
($A3:$A380>=DATE(2012,2,1))*
($A3:$A380<DATE(2012,3,1))*
($B3:$B380))
[/tt]
If you say, "well my data will only be for one year, never multiple years," then I suspect that you tend to chop up your data (for instance, by year), which is another really bad spreadsheet practice that severly limits the usefulness of your data, rendering many Excel features useless. Just another thought.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

or...

=SUMPRODUCT((MONTH($A3:$A380)=2)*(YEAR($A3:$A380)=2012)*($B3:$B380))

 

[Ronnykap]

How about trying Data, Subtotals? Sort by Year/Date, then go to Data, Subtotal and subtotal by Month field checking the Currency column in which to create subtotals. It will then give you subtotals for each month. You my need to create a column next to your date column to indicate month only, if that is what you want to subtotal by.
Ronny Kaplan, Trainer

 

[firefytr]

Hi there,

Have you thought about using a PivotTable for this? I'm not sure why you're wanting this, or what your data source looks like, but they're pretty handy-dandy.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci