Sum Second Occurrence, if there is one, with criteria

[gberg]

I am trying to get a formula that will take the second occurrence of a name in column A. If there is no second occurrence then the unique value should be used. The other part of it is that each name will start with a letter of the alphabet (i.e. A, B, C, D, etc.) I need the formula to sum only the "D's" or "E's" together. I have found a formula that accomplishes the first goal of taking the second occurrence but I can't make it look up the "D" or "E" to group by these designations. On the following example I have highlighted the items that I want summed. Cells C25 and C26 are the outcomes I am looking for. Any help would be greatly appreciated.

Book1.xlsx
	A	B	C
1	TOTAL VALUE
2	Project	Value
3	D0001	100,000
4	D0002	110,000
5	D0003	120,000
6	D0004	130,000
7	E0001	140,000
8	E0002	150,000
9	E0003	160,000
10	E0004	170,000
11	E0005	180,000
12	E0006	190,000
13
14	WORK IN PLACE
15	Project	Value
16	D0001	1,000
17	D0004	4,000
18	E0001	5,000
19	E0002	6,000
20	E0005	9,000
21	E0006	10,000
22
23
24
25	D	595,000	235,000
26	E	595,000	360,000
Sheet1

Cell Formulas
Range	Formula
B25	B25	=SUM(IF((COUNTIF(A3:A21,A3:A21)>1),IF(MATCH(A3:A21,A3:A21,0)<>ROW(A3:A21)-MIN(ROW(A3:A21))+1,B3:B21),B3:B21))
C25	C25	=B4+B5+B16+B17
B26	B26	=SUM(IF((COUNTIF(A3:A21,A3:A21)>1),IF(MATCH(A3:A21,A3:A21,0)<>ROW(A3:A21)-MIN(ROW(A3:A21))+1,B3:B21),B3:B21))
C26	C26	=B9+B10+B18+B19+B20+B21

 

[gberg]

You all have been a big help, thanks for everyone's assistance. I've tried both and they work well and have me all set!

 

[Fluff]

Glad we could help & thanks for the feedback.

I had taken the statement in post 1 as meaning that there could be more than 2 occurrences

Interesting how we can all see things differently, as I took it to mean there would only be one or two occurrences.

 

[jasonb75]

I took it to mean there would only be one or two occurrences.

In hindsight, I think you're correct.

 

[jasonb75]

as I took it to mean there would only be one or two occurrences.

With that in mind, I went back to finish an idea that I abandoned earlier. I was trying similar with XLOOKUP but it refuses to return a 2 column array ?

Book1
	A	B
1	TOTAL VALUE
2	Project	Value
3	D0001	100000
4	D0002	110000
5	D0003	120000
6	D0004	130000
7	E0001	140000
8	E0002	150000
9	E0003	160000
10	E0004	170000
11	E0005	180000
12	E0006	190000
13
14	WORK IN PLACE
15	Project	Value
16	D0001	1000
17	D0004	4000
18	E0001	5000
19	E0002	6000
20	E0005	9000
21	E0006	10000
22
23
24
25	D	235000
26	E	360000
Sheet2

Cell Formulas
Range	Formula
B25:B26	B25	=SUM(UNIQUE(FILTER(INDEX($A$3:$B$21,XMATCH($A$3:$A$21,$A$3:$A$21,0,-1),{1,2}),LEFT($A$3:$A$21)=A25,"")))

 

[Fluff]

I was trying similar with XLOOKUP but it refuses to return a 2 column array

Interesting, it does if you lookup a single value, but not if you lookup an array ?

 

[jasonb75]

Maybe because in there is only 1 row to return with a single value?

I've got a working version by changing the order of events in the formula. By filtering first, this should improve efficiency as well as there are less exact matches to process.

=SUM(XLOOKUP(UNIQUE(FILTER($A$3:$A$21,LEFT($A$3:$A$21)=A25)),$A$3:$A$21,$B$3:$B$21,,0,-1))

 

[Eric W]

Here's a slightly longer way, but it handles more than 2 values:

=SUM(FILTER(B$3:B$21,(LEFT(A$3:A$21)=A25)*((MATCH(A$3:A$21&"",A$3:A$21&"",0)<SEQUENCE(ROWS(A$3:A$21)))+(COUNTIF(A$3:A$21,A$3:A$21)=1))))