search in the entire sheet for different letter and cal.

P

Peter FS

Hello everyone, I am looking for a formula that can look up several different
letter combinations and calculate the values in the corresponding cells. An
example:

There is a host of letters such as "AAB", "BMN", etc. in multiple cells at
the coordinates of 13 columns and multiple rows. Each of those cells has an
adjacent cell that contains a value.

I need the formula to sum the values of the cells that match each of those
particular letter combinations.

Appreciate your help
 
D

Domenic

Assuming that A1:L10 contains your data...

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:K10,{"AAB","BMN"},0))),B1:L10)

OR

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:K10,N1:N2,0))),B1:L10)

....where N1:N2 contains your list of letters, such as 'AAB". You can
add more letters for which to search in both formulas.

For the first formula, you can add them to the array constant
{"AAB","BMN","BBC","CCG",...}. For the second formula, you can add to
the list N1:N2. Make sure you change the reference accordingly.

Hope this helps!
 
B

bj

if your data is in A1:M200
and you are only interested in the values to the right of the matching cells
try
=sumproduct(--(A1:L200="AAB"),B1:M200)
 
B

Barb R.

First, tell us the letter combinations you want to check.
Second, tell us the "coordinates" that you want to check.
 
P

Peter FS

Domenic said:
Assuming that A1:L10 contains your data...

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:K10,{"AAB","BMN"},0))),B1:L10)

OR

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:K10,N1:N2,0))),B1:L10)

....where N1:N2 contains your list of letters, such as 'AAB". You can
add more letters for which to search in both formulas.

For the first formula, you can add them to the array constant
{"AAB","BMN","BBC","CCG",...}. For the second formula, you can add to
the list N1:N2. Make sure you change the reference accordingly.

Hope this helps!
Click to expand...
 
P

Peter FS

I appreciate the reply by you and others. It may well be because I did not
make myself clear so none of the formulae worked. Why don't I copy a portion
of the sheet and show you folks how it looks like

AAB 4 BMN 12 AAB 8 AAB 11
AAB 5 BMN 32 BMN 6 UWO 23
BMN 67 VBN 4 UWO 45 BMN 4
BMN 34 AAB 5 AAB 3 AAB 7
BMN 2 BMN 7 AAB 2 BMN 2
BMN 3 AAB 9 AAB 1 BMN 3
UWO 7 AAB 2 UWO 8 BMN 67

The idea is that I can run a function to look up all those same combinations
and provide the sum of the corresponding values in the adjacent cells. Thanks
in advance again!
 
D

Domenic

Try...

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:G7,{"AAB","BMN"},0))),B1:H7)

OR

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:G7,J1:J2,0))),B1:H7)

....where J1:J2 contains your letters, such as AAB and BMN. In your
example, both these formulas would return 296.

Hope this helps!
 
Top