Results 1 to 10 of 10

SUMIFS or SUMPRODUCT repeat formula for all occurrences of each employee

Threaded View

wmk SUMIFS or SUMPRODUCT repeat... 04-02-2013, 02:54 PM
Pepe Le Mokko Re: SUMIFS or SUMPRODUCT... 04-02-2013, 03:00 PM
wmk Re: SUMIFS or SUMPRODUCT... 04-02-2013, 03:18 PM
wmk Re: SUMIFS or SUMPRODUCT... 04-02-2013, 03:19 PM
wmk Re: SUMIFS or SUMPRODUCT... 04-03-2013, 10:23 AM
Pepe Le Mokko Re: SUMIFS or SUMPRODUCT... 04-03-2013, 12:45 PM
wmk Re: SUMIFS or SUMPRODUCT... 04-03-2013, 12:50 PM
Pepe Le Mokko Re: SUMIFS or SUMPRODUCT... 04-03-2013, 12:52 PM
wmk Re: SUMIFS or SUMPRODUCT... 04-03-2013, 02:06 PM
wmk Re: SUMIFS or SUMPRODUCT... 04-29-2013, 09:45 AM
  1. #1
    Registered User
    Join Date
    04-01-2013
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    SUMIFS or SUMPRODUCT repeat formula for all occurrences of each employee

    Hello again. I'm not exactly an Excel guru (although I seem to be working towards it, no matter how much I fight, lol) and hope one of you folks can help me out on this one.

    Essentially, i have a worksheet that comes in and totals each expenditure for employees over the past few years. Some employees have multiple entries, some have only one. I've inserted two columns, one totaling expenditures in 2011, the other totaling expenditures in 2012. Each needs to total the expenditures for their year from a list of expenditures for each employee name. I have added a SUMIFS formula for the range of cells for the first name, and it works perfectly. However, I need this to apply to each instance of an employee name throughout the sheet, without having to manually enter the formula and new range for each employee. I can't use a pivot table, as the sheet has automatic subtotals incorporated. Not sure if I can do this with SUMIFS, or even SUMPRODUCT.

    Here is the formula for the first name occurrence and the 2011 total:
    =SUMIFS(E2:E6,C2:C6,">=1/1/2011",C2:C6,"<=12/31/2011")

    Here is the forumla for the first name occurrence and the 2012 total:
    =SUMIFS(E2:E6,C2:C6,">=1/1/2012",C2:C6,"<=12/31/2012")

    As stated, both work fine, I just need a way to extend them down through the sheet and apply to whatever cells are under each employee name. I've attached a sample worksheet with representative data. Of course, I could go through each one manually, paste the formula, and change the range, but there are thousands of employees, and I really don't want to spend the next day or so doing nothing but this

    Any help would be greatly appreciated.

    Thank you.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

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