Summing up for specific criteria

I'm hoping someone can help me out here.

I have 2 tables. Each table has 2 columns. In the left hand column is a letter or a letter followed by a number, and in the right hand column is a numeric value that corresponds with the left hand column.

I want to be able to enter multiple letters and/or letters with numbers into a cell, for example A,B,A1,C,D,A3, and have the corresponding values of those letters summed up in the adjacent cell.

I'd imagine it would be a sumproduct but I can't work out how use it for specific criteria.

How would I do this?

excel isnt really designed to have all that stuff in one cell,in would be probably be easier with code

How about

In H3
=SUM(SUMIFS($E$3:$E$12,$D$3:$D$12,{"A","B","C"}))+SUMIFS($B$3:$B$14,$A$3:$A$14,"B1")

In H4
=SUM(SUMIFS($E$3:$E$12,$D$3:$D$12,{"D","E","F"}))+SUM(SUMIFS($B$3:$B$14,$A$3:$A$14,{"A1","A3"}))

In H5
=SUM(SUMIFS($E$3:$E$12,$D$3:$D$12,{"G","H","I","J"}))

Hi maybe pivot table will be the option

Hi PapaCheese,

I'm definetly not one of the formula guru's, but what is wrong with what I posted? As Martin stated, not so sure this can be done with a formula by picking out requirements in one cell seperated by commas. I would imagine a UDF could do this, but not so adept at creating a UDF for this purpose.

with formulas splitting vlook then add this caters for 7 single or double alphas in col g

The formula I was using when I just had the second table was:

So why not add another SUMPRODUCT() formula?

Please Login or Register  to view this content.

[EDIT]
See revised post #11

if you can layout your data in a friendly way, you could achieve the sum a wee bit easily...

Again, not a formula guru, but in just trying to understand the functioning of the Sumproduct method,

what if your table has

A,B1,C
D,E,F,A1

=SUMPRODUCT(--ISNUMBER(SEARCH($A$3:$A$14,G3)), $B$3:$B$14)+SUMPRODUCT(--ISNUMBER(SEARCH($D$3:$D$12, G3)),$E$3:$E$12)

would give you 9 and 16 respectively when it looks like it should be 8 and 15

...unless of course I am missing something

Just noticed that Jeff
It should be

Please Login or Register  to view this content.

if we use the SUMPRODUCT method coupled with SEARCH, the letter value for A is added twice - once for A, once for A1 - if the input string (comma separated multiple values in one cell) has A1.

EDIT:

two things - if you ever have fat-fingered white-spaces, there will be problems in handling that situation with most formulae, except for those where the layout is multi-cellular (each value in a separate cell). also, if you have a situation (i dont know if you will) where an alphabet is repeated (A, A, A1), there the SUMPRODUCT formula does not seem to catch the repetition (the SUM(IF... does). but, what do i know :-), i am still learning.

Originally Posted by marcol

just noticed that jeff
it should be

Please Login or Register  to view this content.

Marcol that's exactly what I was looking for, thanks. And thanks everyone else for taking the time to reply.