Home |
Search |
Today's Posts |
#1
|
|||
|
|||
search in the entire sheet for different letter and cal.
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 |
#2
|
|||
|
|||
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! In article , "Peter FS" <Peter wrote: 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 |
#3
|
|||
|
|||
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) "Peter FS" wrote: 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 |
#4
|
|||
|
|||
First, tell us the letter combinations you want to check.
Second, tell us the "coordinates" that you want to check. "Peter FS" wrote: 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 |
#5
|
|||
|
|||
"Domenic" wrote: 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! In article , "Peter FS" <Peter wrote: 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 |
#6
|
|||
|
|||
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! "Domenic" wrote: 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! In article , "Peter FS" <Peter wrote: 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 |
#7
|
|||
|
|||
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! In article , "Peter FS" wrote: 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! |
#8
|
|||
|
|||
It has worked indeed. Thank you so much, Domenic!
"Domenic" wrote: 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! In article , "Peter FS" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to search in excel with condition | Excel Discussion (Misc queries) | |||
Search and Display Sheet Name | Excel Discussion (Misc queries) | |||
search in a sheet and selection? | Excel Discussion (Misc queries) | |||
Search should highlight entire row in Excell in red | Excel Discussion (Misc queries) | |||
Search for a Sheet | Excel Discussion (Misc queries) |