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

 

[jasonb75]

Here's a way with a helper column, I don't think that it is going to be possible with a single formula but I'll give it a bit more thought.

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

Cell Formulas
Range	Formula
C3:C12,C15:C21	C3	=IF(COUNTIF($A$3:$A$21,A3)=1,2,COUNTIF(A$3:A3,A3))
B25:B26	B25	=SUMIFS($B$3:$B$21,$A$3:$A$21,A25&"*",$C$3:$C$21,2)
C25	C25	=B4+B5+B16+B17
C26	C26	=B9+B10+B18+B19+B20+B21

 

[gberg]

Jason,
I was hoping to accomplish this without the "helper" column. I was trying to think of someway of using the "LEFT" function to pull the first character but I have struggled to work it into the formula. If you think of something let me know.

Thanks,

Greg

 

[jasonb75]

The first character is not the problem, it's the requirement to find the second occurrence. Most functions will find only the second row starting with "D" (row 4 in the example) as a single result, not the second row for each name starting with "D".

The only way that it might work would be with something like

=SUM(IF(COUNTIFS($A$3:$A$21,$A$3:$A$21,$A$3:$A$21,A25&"*")=1,$B$3:$B$21),IF(COUNTIFS(OFFSET($A$3,,,ROW($A$3:$A$21)-ROW($A$3)+1,1),OFFSET($A$3,,,ROW($A$3:$A$21)-ROW($A$3)+1,1),OFFSET($A$3,,,ROW($A$3:$A$21)-ROW($A$3)+1,1),A25&"*")=2,$B$3:$B$21))

which will not work due to the way that sumifs and countifs kick out errors when used with offset. The errors can usually be ignored and the formula will still return a result, but in this case it appears that it is too complex to work.

 

[Toadstool]

I also just used a worker

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

Cell Formulas
Range	Formula
C3:C21	C3	=COUNTIF($A3:$A$21,A3)
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:C26	C25	=SUMIFS($B$3:$B$21,$A$3:$A$21,$A25&"*",$C$3:$C$21,1)
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))

 

[jasonb75]

It wasn't the error in the function that was the problem, it was me setting it up wrong

Note that this uses volatile (inefficient) functions. You will not be able to do this in a single cell formula without them.

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(IF(COUNTIFS($A$3:$A$21,$A$3:$A$21,$A$3:$A$21,A25&"*")=1,$B$3:$B$21),IF(COUNTIFS(OFFSET($A$3,,,ROW($A$3:$A$21)-ROW($A$3)+1,1),$A$3:$A$21,OFFSET($A$3,,,ROW($A$3:$A$21)-ROW($A$3)+1,1),A25&"*")=2,$B$3:$B$21))

 

[gberg]

Jason,
That formula appears to work without the "helper" column, which is awesome! I don't know much about "volatile (inefficient) functions". How much of a concern is this or is that a question that depends on the spreadsheet it is being used in?

Thanks,

Greg

 

[jasonb75]

How much of a concern is this or is that a question that depends on the spreadsheet it is being used in?

The main factors are going to be the size of the data range and the number of cells containing the formula. It will also depend on any other formulas that use the results of this formula for additional calculations (if there are any). A few formulas with a small data range will not make any difference but a lot of formulas and / or a large data range could mean noticeable delays when you add new data, apply filters, etc.

The link below provides a more detail about volatile functions but to try and explain the principle in a simple way, the raw data for the formula is in A3:B21 so a formula that does not use volatile functions will only recalculate if a change to one of those cells is detected. A formula that uses volatile functions will recalculate when any change is detected, not just one made in that range.

Volatile Excel Functions -Decision Models

Microsoft Excel Calculation Secrets and optimisation tips, calculation methods, calculation sequence, dependencies and memory limits.

www.decisionmodels.com

Due to the added complexity, the single cell formula also uses more steps to calculate the result and uses arrays, where the helper method does not. Both of these points mean additional effort is required to process the single cell formula even without taking the volatile part into consideration.

The question to ask yourself would be whether or not excel is noticeably slower since you added the formulas to your sheet?

Using the same raw data table I've added 2 slight variations to the formula which should be progressively more efficient if you have the necessary functions in your version of office 365, although these will still be less efficient than the helper column.

Book1
	A	B	C
25	D	235000	235000
26	E	360000	360000
Sheet2

Cell Formulas
Range	Formula
B25:B26	B25	=SUM(IF(COUNTIFS($A$3:$A$21,$A$3:$A$21,$A$3:$A$21,A25&"*")=1,$B$3:$B$21,IF(COUNTIFS(OFFSET($A$3,,,SEQUENCE(ROWS($A$3:$A$21)),1),$A$3:$A$21,OFFSET($A$3,,,SEQUENCE(ROWS($A$3:$A$21)),1),A25&"*")=2,$B$3:$B$21)))
C25:C26	C25	=LET(r,OFFSET($A$3,,,SEQUENCE(ROWS($A$3:$A$21)),1),SUM(IF(COUNTIFS($A$3:$A$21,$A$3:$A$21,$A$3:$A$21,A25&"*")=1,$B$3:$B$21,IF(COUNTIFS(r,$A$3:$A$21,r,A25&"*")=2,$B$3:$B$21))))

 

[Fluff]

Another option, without volatile functions

+Fluff 1.xlsm
	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
Master

Cell Formulas
Range	Formula
B25:B26	B25	=SUM(FILTER($B$3:$B$21,(LEFT($A$3:$A$21)=A25)*((ROW($A$3:$A$21)-ROW($A$3)+1<>IFNA(MATCH($A$3:$A$21,$A$3:$A$21,0),ROW($A$3:$A$21)))+(ISNUMBER(MATCH($A$3:$A$21,UNIQUE($A$3:$A$21,,1),0))))))

 

[jasonb75]

take the second occurrence of a name in column A.

Looking at @Fluff's suggestion, this may be my fault for overthinking and not asking the question but I had taken the statement in post 1 as meaning that there could be more than 2 occurrences. If this is the case then it would include all but the first occurrence rather than only the second as requested.