# Sum of Most Recent for Each Unique Item

1. ## 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  Register To Reply

2. ## 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)  Register To Reply

3. ## 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  Register To Reply

4. ## 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.  Register To Reply

5. ## 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).  Register To Reply

6. ## Re: Sum of Most Recent for Each Unique Item

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