Sum Second Occurrence, if there is one, with criteria

gberg

Board Regular
Joined
Jul 16, 2014
Messages
75
Office Version
  1. 365
Platform
  1. Windows
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
ABC
1TOTAL VALUE
2Project Value
3D0001100,000
4D0002110,000
5D0003120,000
6D0004130,000
7E0001140,000
8E0002150,000
9E0003160,000
10E0004170,000
11E0005180,000
12E0006190,000
13
14WORK IN PLACE
15Project Value
16D00011,000
17D00044,000
18E00015,000
19E00026,000
20E00059,000
21E000610,000
22
23
24
25D595,000235,000
26E595,000360,000
Sheet1
Cell Formulas
RangeFormula
B25B25=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))
C25C25=B4+B5+B16+B17
B26B26=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))
C26C26=B9+B10+B18+B19+B20+B21
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
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
ABC
1TOTAL VALUE
2Project Value
3D00011000001
4D00021100002
5D00031200002
6D00041300001
7E00011400001
8E00021500001
9E00031600002
10E00041700002
11E00051800001
12E00061900001
13
14WORK IN PLACE
15Project Value 2
16D000110002
17D000440002
18E000150002
19E000260002
20E000590002
21E0006100002
22
23
24
25D235000235000
26E360000360000
Sheet2
Cell Formulas
RangeFormula
C3:C12,C15:C21C3=IF(COUNTIF($A$3:$A$21,A3)=1,2,COUNTIF(A$3:A3,A3))
B25:B26B25=SUMIFS($B$3:$B$21,$A$3:$A$21,A25&"*",$C$3:$C$21,2)
C25C25=B4+B5+B16+B17
C26C26=B9+B10+B18+B19+B20+B21
 

gberg

Board Regular
Joined
Jul 16, 2014
Messages
75
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=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

Well-known Member
Joined
Mar 5, 2018
Messages
2,198
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I also just used a worker

GBerg.xlsx
ABC
1TOTAL VALUE
2Project Value Worker
3D00011000002
4D00021100001
5D00031200001
6D00041300002
7E00011400002
8E00021500002
9E00031600001
10E00041700001
11E00051800002
12E00061900002
130
14WORK IN PLACE1
15Project Value 1
16D000110001
17D000440001
18E000150001
19E000260001
20E000590001
21E0006100001
22
23
24
25D1485000235000
26E1485000360000
Sheet1
Cell Formulas
RangeFormula
C3:C21C3=COUNTIF($A3:$A$21,A3)
B25B25=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:C26C25=SUMIFS($B$3:$B$21,$A$3:$A$21,$A25&"*",$C$3:$C$21,1)
B26B26=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

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
It wasn't the error in the function that was the problem, it was me setting it up wrong :oops:

Note that this uses volatile (inefficient) functions. You will not be able to do this in a single cell formula without them.
Book1
AB
1TOTAL VALUE
2Project Value
3D0001100000
4D0002110000
5D0003120000
6D0004130000
7E0001140000
8E0002150000
9E0003160000
10E0004170000
11E0005180000
12E0006190000
13
14WORK IN PLACE
15Project Value
16D00011000
17D00044000
18E00015000
19E00026000
20E00059000
21E000610000
22
23
24
25D235000
26E360000
Sheet2
Cell Formulas
RangeFormula
B25:B26B25=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

Board Regular
Joined
Jul 16, 2014
Messages
75
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
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.


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
ABC
25D235000235000
26E360000360000
Sheet2
Cell Formulas
RangeFormula
B25:B26B25=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:C26C25=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
Another option, without volatile functions
+Fluff 1.xlsm
AB
1TOTAL VALUE
2Project Value
3D0001100000
4D0002110000
5D0003120000
6D0004130000
7E0001140000
8E0002150000
9E0003160000
10E0004170000
11E0005180000
12E0006190000
13
14WORK IN PLACE
15Project Value
16D00011000
17D00044000
18E00015000
19E00026000
20E00059000
21E000610000
22
23
24
25D235000
26E360000
Master
Cell Formulas
RangeFormula
B25:B26B25=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))))))
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,189
Messages
5,745,243
Members
423,936
Latest member
Conservatopia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top