Sum last occurrences of unique values in column

[natesreich]

Hi All -

Thanks for this great community.

I am trying to sum the last occurrences of unique values in a column for "company1".

For example:



Expected output: 1+2=3

So, sum if:
- Column A = "company1"
- Is last occurrence of the unique value in column B

I imagine it should be something like:
=SUMIFS(C:C,A:A,"company1",IS_LAST_OCCURRENCE_TRUE_OR_FALSE(B:B))

But, what can I use for IS_LAST_OCCURRENCE_TRUE_OR_FALSE?

 

[jasonb75]

Personally, I would use a helper column. It may be possible without if you have the UNIQUE and FILTER functions available. (Please update your account details to show which version of excel you are using by clicking your user name at the top right of this page, then going to 'Account Details', checking the box for your version of excel, then scrolling down and saving changes).

Book1
	A	B	C	D
1	company1	a	1	2
2	company1	b	1	1
3	company2	c	1	1
4	company1	a	2	1
5
6				3
Sheet1

Cell Formulas
Range	Formula
D1:D4	D1	=COUNTIFS(A1:A$4,A1,B1:B$4,B1)
D6	D6	=SUMIFS($C$1:$C$4,$A$1:$A$4,"Company1",$D$1:$D$4,1)

If there is existing data in other columns then there might be something that could be used as an alternative to the helper column.

 

[pgc01]

Hi

I agree with Jason, it makes all the sense to use an auxiliary column, the solution is simple and easy to maintain.

Just for fun this is a solution without the new functions lile unique() or filter().

In H5:

=SUM(IF(IF($C$5:$C$100=G5;COUNTIFS(OFFSET($C$5:$C$100;ROW($C$5:$C$100)-ROW($C$5);;ROWS($C$5:$C$100)-(ROW($C$5:$C$100)-ROW($C$5))+1);$C$5:$C$100;OFFSET($D$5:$D$100;ROW($C$5:$C$100)-ROW($C$5);;ROWS($C$5:$C$100)-(ROW($C$5:$C$100)-ROW($C$5))+1);$D$5:$D$100);0)=1;$E$5:$E$100;0))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.
Copy down.

Book1
	A	B	C	D	E	F	G	H	I
1
2
3
4
5			Company A	a	1		Company A	29
6			Company A	a	2		Company B	22
7			Company A	b	3		Company C	11
8			Company B	b	4
9			Company A	c	5
10			Company B	c	6
11			Company A	c	7
12			Company A	d	8
13			Company A	e	9
14			Company B	e	10
15			Company C	e	11
16			Company B	e	12
17
18
Sheet4