Adding multiple values in a single cell

[lclark13]

I'm wanting to get a count of values. The values are in text form with alpha and numerical, example: AL1, FF2. I looked at SUMIF and SUMPRODUCT to potentially accomplish this.

I'm wondering the best course of action to get a total. Here's what I'm looking to do...

I have a list of reports labeled by the ID assigned.

I have an ID in ColumnA, a definition of the ID in ColumnB, and additional ID's that match the definition in ColumnD.

Sheet1: ColumnA has value of CR3. ColumnD has values of CR12, FF5, SS1 and LO12. I need to be able to add up the number of ID's that match the definition.

<<Sheet 2 contains ID name and duplicate count of that ID>>

Sheet2: ColumnC has a number value that needs to be added to the above count when ColumnA = the values of Sheet1 ColumnA and ColumnD (example, Sheet2:ColumnA = "CR3", "CR12", "FF5", "SS1", "LO12") and ADD that number in ColumnC to the total from Sheet 1.

Sheet2 involves an actual number.

Essentially I'm attempting to total multiple text values in a single cell seperated by a comma along with a single text value of another cell and then match the text values to another sheet.

Any help? I figure this would probably take nested formulas/functions to accomplish. Thanks in advance!

 

[Snakehips]

lclark13,

Welcome to MrExcel.

I have to admit that I am not 100% sure what you are wanting to do re sheet2. Perhaps you can clarify?

In the meantime, perhaps this will gve you the count you want of IDs in sheet 1 column D + 1 for the ID in column A

For eg row 1

=LEN(TRIM(D1))-LEN(SUBSTITUTE(TRIM(D1)," ",""))+2

That assumes your IDs are separated by " " or ", " (space or comma space) with no trailing comma.
It can be rewritten to work purely on comma if you wish.

Hope that helps.