SUMIF values based on each year, 2011, 2012, etc.

[BigD]

Here is my setup:

5-1-2011 $400
6-2-2011 $100
7-1-2012 $200
9-1-2012 $400

How do I add totals for year 2011 and 2012? This is what I have and not working:

=sumif(A1:A4,YEAR=2011,B1:B4)

I know it has to do something with the year fomula because I can change my a1:a4 data to 2011 and modify the formula and it works.

Thank you for viewing my thread.

Dustin

 

[Luke M]

Hi Dustin!

You could either add a helper column with formula:
=YEAR(A2)
in it, and then base the SUMIFS off of that, or you can use a single formula.
One year:
=SUMPRODUCT((B1:B4)*(YEAR(A1:A4)=2011))
Both years:
=SUMPRODUCT((B1:B4)*((YEAR(A1:A4)=2011)+(YEAR(A1:A4)=2012)))

 

[BigD]

UPDATE: Got a pivot table to work by grouping the data, but I would still love the opportunity to learn the formula.

 

[BigD]

Thank you, Luke.

It seems like the formula will be best because the pivot table does not update values as I add more data.

 

[Luke M]

Not quite. If Month is going to enter the picture, I'd suggest using TEXT function. TEXT lets us directly control what format of the data we want to look at.
Sum of June, 2011
=SUMPRODUCT((B1:B4)*(TEXT(A1:A4,"mmyyyy")="062011"))

 

[Colin Legg]

Another formula option:

You could define the start and end dates (inclusive) in two cells, say E1 and F1 and then use either of these:

=SUMIFS(B1:B4,A1:A4,">="&E1,A1:A4,"<="&F1)

or

=SUMIF(A1:A4,">="&E1,B1:B4)-SUMIF(A1:A4,">"&F1,B1:B4)


But I think you were on the right track with a pivot table. If your data is in a table (available XL 2007+) then the pivot table will automatically capture it when it expands (provided the source range is referenced correctly), but then yes you would have to refresh the pivot table to see the new results. The danger with the formula approach is there's a chance you haven't got all of the month/year buckets defined to calculate your sums for.

 

[NARAYANK991]

Hi Dustin ,

I am not sure whether this addresses your question , but see this file.

Narayan