+ Reply to Thread
Results 1 to 6 of 6

Sum of Most Recent for Each Unique Item

  1. #1
    Registered User
    Join Date
    09-23-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Sum of Most Recent for Each Unique Item

    Hi All,

    I have a file that tracks person counts in multiple building, on an almost daily basis. But i can seem to figure out how to get a count of all people in all buildings as of most recent information provided. -- in the table below, the "should be summed" would be counted to total 24
    Date building count should be summed
    3/1/2020 a 4
    3/1/2020 b 4
    3/1/2020 c 4
    3/2/2020 a 5 y
    3/2/2020 b 5 y
    3/2/2020 c 5
    3/2/2020 d 5 y
    3/3/2020 c 6 y
    3/3/2020 e 3 y

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Sum of Most Recent for Each Unique Item

    If you just want to sum the latest date, try a combo MAX/SUMIF, something like...
    =sumif(B:B,MAX(B:B),C:C)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    09-23-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Sum of Most Recent for Each Unique Item

    Thank you, but that wouldn't capture exactly what i need. I would likely get a majority of it, but for building from which my most recent update is one day before the latest update (like buildings A and B). Your formula would return 9, i need 24

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,846

    Re: Sum of Most Recent for Each Unique Item

    Please Login or Register  to view this content.
    **...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Ben Van Johnson

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: Sum of Most Recent for Each Unique Item

    Hi protonLeah,

    Your formula is giving a sum of the maximum counts (column C) for each building (column B), but I think that the OP was rather wanting to sum any counts associated with the latest date for each building. So your formula would need to be:

    =SUM(IF(B2:B10="a",IF(A2:A10=MAX(IF(B2:B10="a",A2:A10)),C2:C10)),IF(B2:B10="b",IF(A2:A10=MAX(IF(B2:B10="b",A2:A10)),C2:C10)),IF(B2:B10="c",IF(A2:A10=MAX(IF(B2:B10="c",A2:A10)),C2:C10)),IF(B2:B10="d",IF(A2:A10=MAX(IF(B2:B10="d",A2:A10)),C2:C10)),IF(B2:B10="e",IF(A2:A10=MAX(IF(B2:B10="e",A2:A10)),C2:C10)))

    Quite lengthy!

    This is quite straightforward with Office 365, since we can employ MAXIFS, viz:

    =SUM(SUMIFS(C2:C10,B2:B10,{"a","b","c","d","e"},A2:A10,MAXIFS(A2:A10,B2:B10,{"a","b","c","d","e"})))

    For Excel 2010, creating an 'array of maximums' is a bit trickier within a single formula, one option being the following array formula**:

    =SUM(SUMIFS(C2:C10,B2:B10,{"a","b","c","d","e"},A2:A10,SMALL(A2:A10,MOD(LARGE({5,4,3,2,1}*10^6+IFERROR(RANK(IF(B2:B10={"a","b","c","d","e"},A2:A10),A2:A10,1),0),{1,10,19,28,37}),10^6))))

    Although this contains several static arrays, it could be modified to a more generalised, dynamic version.

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,846

    Re: Sum of Most Recent for Each Unique Item

    @XOR LX ,
    I double checked with different vals- you're correct...

+ 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. Return the most recent cost when item purchased multiple times
    By VRTOM in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-12-2015, 03:57 PM
  2. [SOLVED] Formula needed - line item of unique item matching criteria
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-18-2015, 06:32 PM
  3. Count Number of days from the most recent item
    By batman1056 in forum Excel General
    Replies: 6
    Last Post: 12-15-2011, 09:57 AM
  4. Each Item's Most Recent/Month/Half Year Average Price
    By jo3c in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-27-2011, 12:01 PM
  5. Replies: 4
    Last Post: 03-18-2011, 09:13 AM
  6. Replies: 0
    Last Post: 01-09-2011, 06:04 PM
  7. [SOLVED] clipboard in Excel collects but will only paste most recent item
    By Gregory1558 in forum Excel General
    Replies: 1
    Last Post: 05-17-2005, 02:06 PM

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