SUMIFS Calculation for Cumulative Annual Wages

Howdy all,

I have designed a basic Excel spreadsheet to calculate my real wages, based on my monthly salary and the host of different deductions and thresholds the UK government has for me that looks something like this (don't worry, it's all dummy data):
Table.png

I am trying to create a column to calculate the cumulative annual wage after deductions, however, my SUMIFS formula is incorrectly constructed and I cannot understand where to go from here. The logic I would like the formula to follow is as follows:

"For Year = X, then SUM all 'Monthly After Deductions' values in X" where X is the corresponding year of that row, which will dynamically change as more rows are added to the table for each new month.

In this example, this would make the first and second rows in that column equal to £2,811.78, and the third row onward in that column equal to £8,016.26.

I would greatly appreciate if anyone could help me structuring a formula that will perform such an action. I have attached this dummy database below for your further understanding.

Thank you very much in advance for your help.

EDIT: Solved now. /closed

Maybe try at E3

=SUMIFS(Taxes1[Monthly After Deductions],Taxes1[Year],B3)

I don't see where exactly you want it but the function you're asking for is pretty simple:

Please Login or Register  to view this content.

I've hardcoded the year reference as 2019 in red, but you could make that a cell reference or whatever.

Note I only used SUMIF not SUMIFS, which has marginally different syntax, assuming you wanted to add more boolean tests:

Please Login or Register  to view this content.

The case I'm imagining you'd need to go to SUMIFS, is you've got year+month in the same cell so you have to bound it as SUMIFS(pay, date_range, " >= start of this year", date_range "< start of next year") or something like that.

Also this is unsolicited, but I think you could TAX and NI columns into simple lookup tables instead of that horrid nest of IF statements.

Please try at
H3
=SUMPRODUCT(TEXT(G3-N(+Rates_20_21!$D$3:$D$6),"General;\0;0")*(Rates_20_21!$F$3:$F$6-N(+Rates_20_21!$F$2:$F$5)))

I3
=SUMPRODUCT(TEXT(G3-N(+Rates_20_21!$D$9:$D$11),"General;\0;0")*(Rates_20_21!$F$9:$F$11-N(+Rates_20_21!$F$8:$F$10)))

Originally Posted by doubleuson

If you could give me a solid nudge in the right direction (ie an example for NI, I can then reapply it for the more complex tax), would appreciate.

Ugh I nerd-sniped myself because it's a bracket calculation instead of a simple lookup, now that I approach again.

Can still be simplified though by burying sophisticated logic in SUMPRODUCT

Please Login or Register  to view this content.

Second column is (Income - bracket row) * third column = (row percentage) and use the first column to force negative results to zero.
(Eg with 1666 income, the third bracket row is 16666 - 4156 < 0, so you tell it to ignore the zero number.

@ ben_hensel

nerd-snipe ?

LOL. Loved it.

Sounds like a snappy name for a competing forum.

Originally Posted by FlameRetired

nerd-snipe ?

LOL. Loved it.

Can't take credit for this turn of phrase, it's from XKCD: