Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF / SUM - Nested Formula Required?
I have a spreadsheet with 17 rows, each representing a project name. In
the columns, I have 2 cells for each week: Est and Actual. At the end of each row, I would like a row total for Est and another for Actual. As there are 20+ weeks represented, I cannot select the cells that have the criteria I'm looking for without using a more complex formula than SUM. Any ideas? The spreadsheet looks like this: Project Name Apr 3-9 Apr 10-16 Apr 17-23 Total Est | Actual Est | Actual Est | Actual E | A Project A 20 | 22 40 | 37 25 | 26 85 | 85 Project B 41 | 40 27 | 33 27 | 29 95 | 102 Project C 10 | 12 12 | 12 14 | 11 36 | 35 In the Total Est cell, I only want to see the total Est for that row (project). In the Total Actual cell I only want to see the total Actual for that row (project). |
#2
|
|||
|
|||
Assumptions:
1) Row 1 contains the headers/labels Project Name, Apr 3-9, etc. 2) Row 2 contains your headers/labels Est, Actual, etc., starting at B2 3) H2 contains the header/label Est 4) I2 contains the header/label Actual 5) A4:G6 contains your data Formula: H4, copied down and over to the next column: =SUMPRODUCT(--($B$2:$G$2=H$2),$B4:$G4) Adjust the ranges accordingly. Hope this helps! In article .com, "Annabelle" wrote: I have a spreadsheet with 17 rows, each representing a project name. In the columns, I have 2 cells for each week: Est and Actual. At the end of each row, I would like a row total for Est and another for Actual. As there are 20+ weeks represented, I cannot select the cells that have the criteria I'm looking for without using a more complex formula than SUM. Any ideas? The spreadsheet looks like this: Project Name Apr 3-9 Apr 10-16 Apr 17-23 Total Est | Actual Est | Actual Est | Actual E | A Project A 20 | 22 40 | 37 25 | 26 85 | 85 Project B 41 | 40 27 | 33 27 | 29 95 | 102 Project C 10 | 12 12 | 12 14 | 11 36 | 35 In the Total Est cell, I only want to see the total Est for that row (project). In the Total Actual cell I only want to see the total Actual for that row (project). |
#3
|
|||
|
|||
Assuming your column titles are in cells a1 to f1 and your data in b2 to f2,
then the following should work: =SUMIF(A1:F1,"Act",A2:F2). Act Est Act Est Act Est 2 3 4 5 6 7 will give the result 12 HTH. Huw. "Annabelle" wrote: I have a spreadsheet with 17 rows, each representing a project name. In the columns, I have 2 cells for each week: Est and Actual. At the end of each row, I would like a row total for Est and another for Actual. As there are 20+ weeks represented, I cannot select the cells that have the criteria I'm looking for without using a more complex formula than SUM. Any ideas? The spreadsheet looks like this: Project Name Apr 3-9 Apr 10-16 Apr 17-23 Total Est | Actual Est | Actual Est | Actual E | A Project A 20 | 22 40 | 37 25 | 26 85 | 85 Project B 41 | 40 27 | 33 27 | 29 95 | 102 Project C 10 | 12 12 | 12 14 | 11 36 | 35 In the Total Est cell, I only want to see the total Est for that row (project). In the Total Actual cell I only want to see the total Actual for that row (project). |
#4
|
|||
|
|||
As Huw has pointed out in his solution, there's no need to use
SUMPRODUCT when SUMIF will suffice. Therefore, use the following formula instead... H4, copied down and over to the next column: =SUMIF($B$2:$G$2,H$2,$B4:$G4) Hope this helps! In article , Domenic wrote: Assumptions: 1) Row 1 contains the headers/labels Project Name, Apr 3-9, etc. 2) Row 2 contains your headers/labels Est, Actual, etc., starting at B2 3) H2 contains the header/label Est 4) I2 contains the header/label Actual 5) A4:G6 contains your data Formula: H4, copied down and over to the next column: =SUMPRODUCT(--($B$2:$G$2=H$2),$B4:$G4) Adjust the ranges accordingly. Hope this helps! In article .com, "Annabelle" wrote: I have a spreadsheet with 17 rows, each representing a project name. In the columns, I have 2 cells for each week: Est and Actual. At the end of each row, I would like a row total for Est and another for Actual. As there are 20+ weeks represented, I cannot select the cells that have the criteria I'm looking for without using a more complex formula than SUM. Any ideas? The spreadsheet looks like this: Project Name Apr 3-9 Apr 10-16 Apr 17-23 Total Est | Actual Est | Actual Est | Actual E | A Project A 20 | 22 40 | 37 25 | 26 85 | 85 Project B 41 | 40 27 | 33 27 | 29 95 | 102 Project C 10 | 12 12 | 12 14 | 11 36 | 35 In the Total Est cell, I only want to see the total Est for that row (project). In the Total Actual cell I only want to see the total Actual for that row (project). |
#5
|
|||
|
|||
Yes, the SUMIF statement worked great, thank you! Now, I'll go back and
figure out HOW this formula works. Thanks again to you both. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Frequency formula | Excel Worksheet Functions | |||
Multiple Condition Sumif Formula | Excel Worksheet Functions | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions | |||
Price rounding - exception formula required | Excel Worksheet Functions |