Using SUMIF for multiple values separated by comma??

HockeyDiablo

Board Regular
Joined
Apr 1, 2016
Messages
180
I will have column information that will list entries like this:
DD, DD9, SD, LHR, HIGHLIFT
BOTTOM SECTION, DD, D9
DD, PVC DD, SD, LHR, HIGHLIFT

Is there a way to run a SUMIF or other formula on these and separate the values from the commas? I am familiar with Text to Columns but I will be using 22 unique values "DD, DD9..."


DD0.3
DD90.5
SD0.8
LHR1.3
HIGHLIFT1.5
BOTTOM SECTION2

<colgroup><col><col></colgroup><tbody>
</tbody>

So the outcome would look something like this.

DD, DD9, SD, LHR, HIGHLIFT 4.25
BOTTOM SECTION, DD, D9 2.75
DD, SD, LHR, HIGHLIFT 3.75

Appreciate it.
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
The below has 2 similar options. Personally I'd go with SUMPRODUCT.

Excel Workbook
ABCDEFGHI
1DD0.3
2DD90.5
3SD0.8
4LHR1.3
5HIGHLIFT1.5
6BOTTOM SECTION2
7
8
9
10DDDD9SDLHRHIGHLIFT
11BOTTOM SECTIONDD
12DDSDLHRHIGHLIFT
Sheet1
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
Welcome to Mr Excel

Maybe this


A
B
C
D
E
1
Code​
Value​
Codes​
Total​
2
DD​
0,3​
DD, DD9, SD, LHR, HIGHLIFT​
4,4​
3
DD9​
0,5​
BOTTOM SECTION, DD, DD9​
2,8​
4
SD​
0,8​
DD, SD, LHR, HIGHLIFT​
3,9​
5
LHR​
1,3​
6
HIGHLIFT​
1,5​
7
BOTTOM SECTION​
2​

Formula in E2 copied down
=SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$7&",",D2&",")),$B$2:$B$7)

Hope this helps

M.
 

HockeyDiablo

Board Regular
Joined
Apr 1, 2016
Messages
180
That absolutely worked for me. Now testing time. Thank you so much for your time in helping me with this.
 

Forum statistics

Threads
1,082,478
Messages
5,365,789
Members
400,851
Latest member
FrankNStein

Some videos you may like

This Week's Hot Topics

Top