|
Match 2 Criteria
I need help with a formulas to populate a table that has Project
numbers on sheet1, range(a5:a120) that I want to match to sheet2, range(a5:a17244) and return the sum of values, sheet2, range(f5:f17242) that match Jan, entered as 1, found in sheet2, range(i5:i17244). Then repeat for Feb, Mar, etc. Any help would be greatly appreciated. TIA Greg |
Hi!
Here's what I think you're asking for... In sheet1 B5 enter this formula and copy down to B120: =SUMPRODUCT(--(Sheet2!A$5:A$17244=A5),--(Sheet2!I$5:I$17244=1),Sheet2!F$5:F$17244) For the other months: Feb, Mar, Apr etc, replace the 1 in the formula with the corresponding month number or enter the month number in a cell and reference that cell: =SUMPRODUCT(--(Sheet2!A$5:A$17244=A5),--(Sheet2!I$5:I$17244=A$1),Sheet2!F$5:F$17244) Biff "GregR" wrote in message oups.com... I need help with a formulas to populate a table that has Project numbers on sheet1, range(a5:a120) that I want to match to sheet2, range(a5:a17244) and return the sum of values, sheet2, range(f5:f17242) that match Jan, entered as 1, found in sheet2, range(i5:i17244). Then repeat for Feb, Mar, etc. Any help would be greatly appreciated. TIA Greg |
Biff, not only did you figure out the answer you figured out my question,
which may have been much harder. Thank you Greg "Biff" wrote in message ... Hi! Here's what I think you're asking for... In sheet1 B5 enter this formula and copy down to B120: =SUMPRODUCT(--(Sheet2!A$5:A$17244=A5),--(Sheet2!I$5:I$17244=1),Sheet2!F$5:F$17244) For the other months: Feb, Mar, Apr etc, replace the 1 in the formula with the corresponding month number or enter the month number in a cell and reference that cell: =SUMPRODUCT(--(Sheet2!A$5:A$17244=A5),--(Sheet2!I$5:I$17244=A$1),Sheet2!F$5:F$17244) Biff "GregR" wrote in message oups.com... I need help with a formulas to populate a table that has Project numbers on sheet1, range(a5:a120) that I want to match to sheet2, range(a5:a17244) and return the sum of values, sheet2, range(f5:f17242) that match Jan, entered as 1, found in sheet2, range(i5:i17244). Then repeat for Feb, Mar, etc. Any help would be greatly appreciated. TIA Greg |
You're welcome! Thanks for the feedback.
Biff "GregR" wrote in message ... Biff, not only did you figure out the answer you figured out my question, which may have been much harder. Thank you Greg "Biff" wrote in message ... Hi! Here's what I think you're asking for... In sheet1 B5 enter this formula and copy down to B120: =SUMPRODUCT(--(Sheet2!A$5:A$17244=A5),--(Sheet2!I$5:I$17244=1),Sheet2!F$5:F$17244) For the other months: Feb, Mar, Apr etc, replace the 1 in the formula with the corresponding month number or enter the month number in a cell and reference that cell: =SUMPRODUCT(--(Sheet2!A$5:A$17244=A5),--(Sheet2!I$5:I$17244=A$1),Sheet2!F$5:F$17244) Biff "GregR" wrote in message oups.com... I need help with a formulas to populate a table that has Project numbers on sheet1, range(a5:a120) that I want to match to sheet2, range(a5:a17244) and return the sum of values, sheet2, range(f5:f17242) that match Jan, entered as 1, found in sheet2, range(i5:i17244). Then repeat for Feb, Mar, etc. Any help would be greatly appreciated. TIA Greg |
| All times are GMT +1. The time now is 10:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com