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).

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).

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).

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).

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.