SUMIFS (multiple criteria in one cell)

[tpenney89]

Hi All,

I have to create multiple dashboards for a client and I'm looking to streamline our process. One of the tabs used in the dashboard is a summary of costs based on multiple criteria. Right now it is set up that each entity the client is looking to analyze has their own tab however I'm looking to eliminate this.

Each entity has a unique 6 character alpha numeric number, we call them C#s (example: C12345).

The idea is that in cell A1 the C# we are looking to analyze is in this cell and each formula reads this cell.

I want to create a drop down (I DO NOT need help with the drop down) that will list all possible combinations so the user only needs to make one selection. so for example the drop down will have the below options

C12345
C98765
C19283
C12345, C98765
C12345, C98765, C19283

What Im having trouble with is the multiple C#s, so my question

Is there a way to tell excel to sum C12345 AND C98765 when separated by a comma?

I have possible solutions in my head that include 5 if statements or doing VBA however my co workers are somewhat excel illiterate and I need to make the formula as simple as possible in case C#s end up getting added or removed.

Any insight on this would be helpful, I don't work with many text formulas so there may be a formula I am not thinking of. Thanks!

 

[NiMip]

Here's a simple example that you might be able to adjust to your spreadsheet? You have to hit CONTROL+SHIFT+ENTER for the formula to work, as it's an array formula. You should see {} surround the formula if done correctly.

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Lookup Criteria[/TD]
[TD]Sum[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C1, E3, B6, Z3[/TD]
[TD="align: right"]=SUM(IF(ISNUMBER(SEARCH($A$8:$A$18,A2)),$B$8:$B$18))[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]G2, F3[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]S9[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Criteria List[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]C1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]E2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]F3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]E3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Z3[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]R8[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]S9[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]B6[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]G2[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]N6[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]X8[/TD]
[TD="align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]