+ Reply to Thread
Results 1 to 12 of 12

SUMIFS Calculation for Cumulative Annual Wages

Hybrid View

  1. #1
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: SUMIFS Calculation for Cumulative Annual Wages

    I don't see where exactly you want it but the function you're asking for is pretty simple:
    =SUMIF(=SUMIF(Taxes1[Year], 2019, Taxes1[Monthly After Deductions])
    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:
    =SUMIFS(=SUMIF(Taxes1[Monthly After Deductions], Taxes1[Year], 2019)
    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.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  2. #2
    Forum Contributor
    Join Date
    07-31-2018
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    104

    Re: SUMIFS Calculation for Cumulative Annual Wages

    Quote Originally Posted by ben_hensel View Post
    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.
    I would if I knew how to go about it. They get quite fiddly but I was going to leave this part for when the Tax and NI rates change in April next year 😂

    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.

    Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 13
    Last Post: 10-29-2017, 06:00 AM
  2. [SOLVED] Find out the time between 1st day to next day for **** wages calculation
    By vrumapathy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-04-2015, 03:22 AM
  3. [SOLVED] Cumulative total sales with annual growth
    By donkeybusiness in forum Excel General
    Replies: 17
    Last Post: 01-07-2015, 10:53 AM
  4. Annual cost calculation based on changes.
    By sekharyadav in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-21-2013, 01:30 AM
  5. [SOLVED] Calculation of annual leave
    By youngjeesu in forum Excel General
    Replies: 3
    Last Post: 08-10-2012, 01:08 AM
  6. Annual cumulative interest
    By haloperidal in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-28-2012, 11:31 AM
  7. Replies: 0
    Last Post: 08-29-2005, 11:04 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1