|
Add from multiple criteria in a range
I have been struggling with this one, and believe that there must be a way of
doing this without adding 52 different formulas. I have row A with 1 criteria, row B with the second. Columns C,D & E can contain the criteria I am looking for in row A. Then Columns F onwards contain quantity in the first column then Criteria B in the second column, and so on. I need to find the total of all matches of Criteria A in either C,D or E, then Criteria B in column G onwards. Returning the value of the previous colums , then added together. A B C D E F G H I J K L M N FB ORML (FORMULA) FB 0 0 5 VGML 0 0 0 0 FB VGML FM FB 0 2 FPML 1 VGML 0 0 FB FPML LM 0 0 1 VGML 1 ORML 3 VLML FB VLML CB FM FB 3 ORML 2 FPML 0 0 FB MOML FM 0 0 0 O 0 0 0 0 So I need the formula to find all matches of A in D,E & F, then find matches of column b in the corresponding rows, when matches found add up the numbers in the previous cell to the match. I tried sumproduct, but was limited to single columns, thus would have to do over 52 formulas for each line. I know there must be an easier way, but cannot find it. Any help would save me pulling my hair out. Hope this makes sense. |
Add from multiple criteria in a range
It makes a little sense....
Try this for the part about matching B: =SUMIF(F2:BB2,B2,E2:BA2) I'm not sure what you want to do about A, perhaps something like this? =IF(NOT(ISERROR(MATCH(A2,C2:E2,FALSE))),SUMIF(F2:B B2,B2,E2:BA2),"") HTH, Bernie MS Excel MVP "thomsonpa" wrote in message ... I have been struggling with this one, and believe that there must be a way of doing this without adding 52 different formulas. I have row A with 1 criteria, row B with the second. Columns C,D & E can contain the criteria I am looking for in row A. Then Columns F onwards contain quantity in the first column then Criteria B in the second column, and so on. I need to find the total of all matches of Criteria A in either C,D or E, then Criteria B in column G onwards. Returning the value of the previous colums , then added together. A B C D E F G H I J K L M N FB ORML (FORMULA) FB 0 0 5 VGML 0 0 0 0 FB VGML FM FB 0 2 FPML 1 VGML 0 0 FB FPML LM 0 0 1 VGML 1 ORML 3 VLML FB VLML CB FM FB 3 ORML 2 FPML 0 0 FB MOML FM 0 0 0 O 0 0 0 0 So I need the formula to find all matches of A in D,E & F, then find matches of column b in the corresponding rows, when matches found add up the numbers in the previous cell to the match. I tried sumproduct, but was limited to single columns, thus would have to do over 52 formulas for each line. I know there must be an easier way, but cannot find it. Any help would save me pulling my hair out. Hope this makes sense. |
Add from multiple criteria in a range
|
Add from multiple criteria in a range
Sorry, I cannot use Office 2007 version at work.
"Herbert Seidenberg" wrote: Excel 2007 Yet another stab into the dark: http://www.mediafire.com/file/kkynomzntdm/02_04_09.xlsx |
Add from multiple criteria in a range
Sorry I could not get your formulas to work. Maybe I didn't explain it too
well. (Plus I made an error in my column names!). I need to find a match for ROW A in columns D, E or F. if matches are found (there will be more than one) look for a match for column B in that row, for all matches return the value of the previous cell into column C. There will be more than one to add up. So: A B C D E F G H I FB VLML (ANSWER) FM FB 0 12 VLML 1 "Bernie Deitrick" wrote: It makes a little sense.... Try this for the part about matching B: =SUMIF(F2:BB2,B2,E2:BA2) I'm not sure what you want to do about A, perhaps something like this? =IF(NOT(ISERROR(MATCH(A2,C2:E2,FALSE))),SUMIF(F2:B B2,B2,E2:BA2),"") HTH, Bernie MS Excel MVP "thomsonpa" wrote in message ... I have been struggling with this one, and believe that there must be a way of doing this without adding 52 different formulas. I have row A with 1 criteria, row B with the second. Columns C,D & E can contain the criteria I am looking for in row A. Then Columns F onwards contain quantity in the first column then Criteria B in the second column, and so on. I need to find the total of all matches of Criteria A in either C,D or E, then Criteria B in column G onwards. Returning the value of the previous colums , then added together. A B C D E F G H I J K L M N FB ORML (FORMULA) FB 0 0 5 VGML 0 0 0 0 FB VGML FM FB 0 2 FPML 1 VGML 0 0 FB FPML LM 0 0 1 VGML 1 ORML 3 VLML FB VLML CB FM FB 3 ORML 2 FPML 0 0 FB MOML FM 0 0 0 O 0 0 0 0 So I need the formula to find all matches of A in D,E & F, then find matches of column b in the corresponding rows, when matches found add up the numbers in the previous cell to the match. I tried sumproduct, but was limited to single columns, thus would have to do over 52 formulas for each line. I know there must be an easier way, but cannot find it. Any help would save me pulling my hair out. Hope this makes sense. |
Add from multiple criteria in a range
There is no ROW A - there is a column A...
Anyway, try this for values in Row 2 =IF(NOT(ISERROR(MATCH(A2,D2:F2,FALSE))),SUMIF(H2:B B2,B2,G2:BA2),"") Note that in the SUMIF, the ranges are offset by one column - so if you change H2:BB2 to H2:CB2, you need to change G2:BA2 to G2:CA2... The same number of columns in each range. HTH, Bernie MS Excel MVP "thomsonpa" wrote in message ... Sorry I could not get your formulas to work. Maybe I didn't explain it too well. (Plus I made an error in my column names!). I need to find a match for ROW A in columns D, E or F. if matches are found (there will be more than one) look for a match for column B in that row, for all matches return the value of the previous cell into column C. There will be more than one to add up. So: A B C D E F G H I FB VLML (ANSWER) FM FB 0 12 VLML 1 "Bernie Deitrick" wrote: It makes a little sense.... Try this for the part about matching B: =SUMIF(F2:BB2,B2,E2:BA2) I'm not sure what you want to do about A, perhaps something like this? =IF(NOT(ISERROR(MATCH(A2,C2:E2,FALSE))),SUMIF(F2:B B2,B2,E2:BA2),"") HTH, Bernie MS Excel MVP "thomsonpa" wrote in message ... I have been struggling with this one, and believe that there must be a way of doing this without adding 52 different formulas. I have row A with 1 criteria, row B with the second. Columns C,D & E can contain the criteria I am looking for in row A. Then Columns F onwards contain quantity in the first column then Criteria B in the second column, and so on. I need to find the total of all matches of Criteria A in either C,D or E, then Criteria B in column G onwards. Returning the value of the previous colums , then added together. A B C D E F G H I J K L M N FB ORML (FORMULA) FB 0 0 5 VGML 0 0 0 0 FB VGML FM FB 0 2 FPML 1 VGML 0 0 FB FPML LM 0 0 1 VGML 1 ORML 3 VLML FB VLML CB FM FB 3 ORML 2 FPML 0 0 FB MOML FM 0 0 0 O 0 0 0 0 So I need the formula to find all matches of A in D,E & F, then find matches of column b in the corresponding rows, when matches found add up the numbers in the previous cell to the match. I tried sumproduct, but was limited to single columns, thus would have to do over 52 formulas for each line. I know there must be an easier way, but cannot find it. Any help would save me pulling my hair out. Hope this makes sense. |
| All times are GMT +1. The time now is 04:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com