Adding data from specific years

[Marvo]

Hi, I'm trying to total data from specific year. 2009, 2010 and 2011. If I enter the following
=SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2009")
it produces the correct result for 2009
If I then add to the formula
=SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2009")+SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2010")
it produces the correct result for 2009 & 2010
However if I add the third condition
=SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2009")+SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2010")+SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2011")
it no longer produces a result at all AND it also stops the second condition working too.
Any idea why?

I'm sure in all likelihood the formula could be simplified for all three years as it is repeating itself but I don't know how.

Many thanks

 

[Marvo]

I suppose an easier way to ask this is, can/how do I construct these three formulas into one?
=SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2009")
=SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2010")
=SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2011")

 

[Logit]

Untested here but try :

To combine these three formulas into one, you can use the SUM function to sum the results of the three SUMIFS formulas. The combined formula will look like this:

=SUM(
    SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2009"),
    SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2010"),
    SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2011")
)

Alternatively, if the year values are contiguous and you want to avoid repeating the SUMIFS function for each year, you can adjust the criteria to match a range of years using the SUMPRODUCT function:

=SUMPRODUCT(
    (ALL!$N:$N=$B17) * (ALL!$D:$D>="2009") * (ALL!$D:$D<="2011") * (ALL!$L:$L)
)

 

[Marvo]

Thank you. I placed this in the cell

=SUM(SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2009"),SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2010"),SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2011"))

But it produces no result? Not an error, just shows the formula as is?

 

[Marvo]

This is a bit weird. I put the original formula in in one go.

=SUMIFS(ALL!$L:$L,ALL!$N:$N,B17,ALL!$D:$D,"2009")+SUMIFS(ALL!$L:$L,ALL!$N:$N,B17,ALL!$D:$D,"2010")+SUMIFS(ALL!$L:$L,ALL!$N:$N,B17,ALL!$D:$D,"2011")

and it worked producing the correct result. But to then when copying the formula to another cell, not only doesn't it work but you also stop the original working. Same if you alter the formula in even the slightest way like adding a $.

 

[Logit]

You could try this :

=SUMPRODUCT((ALL!$L$2:$L$1000) *
   (ALL!$N$2:$N$1000 = B17) *
   ((ALL!$D$2:$D$1000 = "2009") + (ALL!$D$2:$D$1000 = "2010") + (ALL!$D$2:$D$1000 = "2011")))

 

[Marvo]

Apologies. I've had to give up, weird stuff going on. Its a new lap top and things aren't going to plan. I was deleting stuff from cells. The cells were showing as empty but when I looked at the bar above the stuff was still in there. I've never known anything like it. Sorry for wasting your valuable time.

 

[Logit]

No problem. As far as the data not showing in the cells, what about the font color ?

 

[Marvo]

No, its something else. I put a formula in a cell, then it went awry, so I deleted the formula and the cells shows as empty, until you look in the bar above and its still there. Clear contents, still there, the only way to get rid of it was to cope over a blank cell. I've never known anything like it. I think that's why I couldn't get your formula to work, I think it may have been adding to whatever was already in there. Maybe I've got a dodgy copy of excel, yesterday it wouldn't sort percentages correctly (large to small). Annoying and frustrating.

 

[Logit]

You could uninstall Excel, reboot your computer then reload Excel.

Or you could try repairing Excel first.