#### 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?

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

``` 0 5/10/2005 7:47:22 PM excel.misc  78881 articles. 5 followers. 4 Replies 346 Views Similar Articles

[PageSpeed] 29

```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

Formula:

H4, copied down and over to the next column:

=SUMPRODUCT(--(\$B\$2:\$G\$2=H\$2),\$B4:\$G4)

Hope this helps!

"Annabelle" <a_bolland@yahoo.com> 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).
``` 0 5/10/2005 8:13:01 PM
```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).
>
>
``` 0 5/10/2005 8:22:05 PM
```As Huw has pointed out in his solution, there's no need to use
SUMPRODUCT when SUMIF will suffice.  Therefore, use the following

H4, copied down and over to the next column:

=SUMIF(\$B\$2:\$G\$2,H\$2,\$B4:\$G4)

Hope this helps!

In article <domenic22-094604.16130110052005@msnews.microsoft.com>,
Domenic <domenic22@sympatico.ca> 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)
>
>
> Hope this helps!
>
>  "Annabelle" <a_bolland@yahoo.com> 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).
``` 0 5/10/2005 9:21:37 PM
```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.

``` 0 5/10/2005 9:49:41 PM Similar Artilces:

Referencing two (or more) cell values in formula
Hi, Can anyone tell me the correct syntax for referencing two cells as a criterion in a formula. For example if I want to sum cells in b1:b5 if cells a1:a5 are greater than the value in c2 I would write the following: =sumif(a1:a5,">"&c2,b1:b5) If I want to sum b1:b5 if cells in a1:a5 are greater than the value in c2 and less than c3 what should I write? =sumif(a1:a5,and(">"&c2,"<"&c3),b1:b5) this doesn't work and neither does this.... =sumif(a1:a5,and(>c2,<c3),b1:b5). Any help gratefully received. Thanks, Will willcull@...

I need to create a two part formula
The formula below will return a GPA for our salespeople =IF(H7>0.9,4,IF(H7>=0.8,3,IF(H7>=0.7,2,IF(H7>=0.6,1,0)))), but I need to add one more criteria and that if another cell (C4) = 0 then the results of this formula need to be 0 as well. I hope this makes sense, can someone please help me. Thanks! Something like: =IF(C4=0,0,H7>0.9,4,IF(H7>=0.8,3,IF(H7>=0.7,2,IF(H7>=0.6,1,0))))) Regards, Fred "DMW" <DMW@discussions.microsoft.com> wrote in message news:1F81AAD9-C057-4504-8B01-8C1786BF0DD4@microsoft.com... > The formula below wi...

Pasting cells' value as formula
I have a column where each cell has a formula that constructs a string. How do I copy/paste this to an adjacent column such that the string acts as a formula in the new column's cells? For a simplified example, column B has the formulas =concatenate("=text(",a1,")") =concatenate("=text(",b1,")") =concatenate("=text(",c1,")") etc So that column B's values show =text(123) =text(456) =text(789) etc How do I copy/paste column B to column C so that column C shows the values of these literal formulas 123 (as text, in this ex...

Count values either vba or formula
Sample of what I am trying to achieve: Worksheet1: (column A, B, C, D, E) - source data id,primary region, secondary region, tertiary region, status 111,americas,new york, NA, increase 111,americas,canada,NA, increase 111,americas,mexico,LATAM,increase 111,americas,peru,LATAM,decrease 112,europe,france,WEST,increase 112,europe,spain,WEST,decrease 112,europe,uk,WEST,decrease 112,europe,portugal,WEST,decrease etc.... Worksheet2: (contains summary sheet) - summarize data 111,americas,NA, increase, 2 <----- count instances found from worksheet1 111,americas,LATAM,increase, 1 111,americas,LA...

vlookup formula #4
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C8FEBA.BDA26D90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: base64 SSBoYXZlIHNvbWUgcGFydCBudW1iZXJzIHRoYXQgSSBhbSBzZWFyY2hpbmcgZm9yIGV4dGVybmFs bHkgdXNpbmcgdGhlICJGYWxzZSIgcXVhbGlmaWVyLiAgU29tZSBmaWVsZHMgcmV0dXJuIGEgdmFs dWUgb2YgIiNOL0EiICBhcyB0aGVyZSBpcyBubyB2YWx1ZS4gIENhbiBhbnlvbmUgaGVscCBtZSB3 aXRoIGEgZm9ybXVsYSB0byByZXR1cm4gYSBmaXhlZCB2YWx1ZSAoaS5lLiAxLjApIGlmIHRoZSBm b3JtdWxhIHJldHVybnMgIiNOL0EiIGFzIGEgdmFsdWU/DQoNCg0KVEhhbmtzDQoNClBhdWw= ------=_NextPart_000_0008_01C8FEBA....

How do I write the formula mentioned below to encompass rows 5 - 778 so that it takes care of all at once and then of course this expans on a monthly basis both across columns as new months are included and then down rows as new clients are included. Thanks. select cell D2:X2 (so starting with the third month value) >- in the conditional format dialog enter the following formula: >=(C2<B2)*(D2<C2) >- choose a format Hi just select all these rows and enter the formula =(C5<B5)*(D5<C5) -- Regards Frank Kabel Frankfurt, Germany "corphealth" <anonymo...

Make a formula
Is it possible with Excel 2000 to automatically make a list of all the formulas you are using in your spreadsheet to print them. Thanks...Steve -- Steven E ----------------------------------------------------------------------- Steven E.'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1512 View this thread: http://www.excelforum.com/showthread.php?threadid=26758 This subroutine makes a new sheet (Formulas in Sheen N) in which all formulas are listed together with cell reference and value. Not mine-own but I have lost the reference to the author but I thank h...

Different formatting in a cell with a concatenated formula?
I have a concatenated formula that pulls text from 2 different cells. The 2 cells are formatted differently (i.e. 1 cell has blue text an the other cell has red text and a different font) and I want to kee these formats. However, the cell with the concatenated formula take the format of that cell. Is there anyway to do this -- andy281 ----------------------------------------------------------------------- andy2812's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1596 View this thread: http://www.excelforum.com/showthread.php?threadid=27440 You men to have th...

A formula finds a text in one cell #2
( A ) ( B ) ( C ) ( D ) E ) ( F ) HUNTER-1 ( LARGE 20X24 ) 1 35.37 2.2 S PATTERN CHANGE HUNTER-1 ( LARGE 20X24 ) 1 35.37 1.65 20 CONVEYOR FULL HUNTER-1 ( LARGE 20X24 ) 1 35.37 0.92 01 TURNTABLE HUNTER-1 ( LARGE 20X24 ) 1 35.37 0.4 10 DRAW PINS HUNTER-1 ( LARGE 20X24 ) 1 35.37 0.48 22 DIST. CRANE Without Pattern Cahange 35.37 3.45 With Pattern Change 37.57 5.65 Pattern Change Only 37.57 2. -- vane032 ----------------------------------------------------------------------- vane0326's Profile: http://www.excelforum.com/member....

Formula for formatting rows and columns minimizing the outcome.
I am using Excel 2007. I have 3 columns A, B, and C such as below: TU10-10 TU10 Tungsten Satin Wedding Band TU10-10.5 TU10 Tungsten Satin Wedding Band TU10-11 TU10 Tungsten Satin Wedding Band TU10-11.5 TU10 Tungsten Satin Wedding Band TU10-12 TU10 Tungsten Satin Wedding Band TU10-12.5 TU10 Tungsten Satin Wedding Band TU10-13 TU10 Tungsten Satin Wedding Band TU10-6 TU10 Tungsten Satin Wedding Band TU10-6.5 TU10 Tungsten Satin Wedding Band TU10-7 TU10 Tungsten Satin Wedding Band TU10-7.5 TU10 Tungsten Satin Wedding Band TU10-8 TU10 Tungsten Satin Wedding Band TU10-8.5 TU10 Tung...

Using A Macro/VBA code to re-set formulas
Hi can any one help me with a Macro/VBA code to re-set formulas in pre-defined set of cells i.e. in my case C14:C40 At present I have a copy of the Formulas in another part of th worksheet and when I want to reset the worksheet (as the user can ove right the formulas as the formula is based on either the sum of anothe two cells or the users input) I use a Macro to copy and paste (special the formulas. What I require is a Macro/VBA Code which already has the formula store in the actual Macro/VBA code i.e. so they are not stored as copy in m worksheet Ay help would be greatly appreciated T...

Array Formula isn't summing but counting
I need to sum information based on several other criteria -- accoun number, sort codes, company names. The array formul {=SUM(A1:F25="A")*(E1:E25>0))} returns a count rather than a sum Does anyone know of a way get it to add my data rather than countin how many? Staci -- Message posted from http://www.ExcelForum.com Hi if you want to sum column E try the array formula =SUM((A1:F25="A")*(E1:E25>0)*(E1:E25)) or a non array formula =SUMPRODUCT((A1:F25="A")*(E1:E25>0),E1:E25) -- Regards Frank Kabel Frankfurt, Germany > I need to sum information ...

look up formula
I have two customers with two different list of procedures on one report. cell A1 has a drop down list of the customers when a certain customer is chosen How do I write a formula so that when I choose Cutomer 1 only their list of procedures show up in a drop down box in my procedure area. See Debra's article on dynamic validation: http://www.contextures.com/xldataval02.html -- Best Regards, Luke M "Jane Griffel" <JaneGriffel@discussions.microsoft.com> wrote in message news:E2411A44-0F70-4DA1-91E8-DA9198C5F704@microsoft.com... >I have two customers wi...

Is there anyway to insert a formula into a comment box?? I have one sheet with three columns which contain defect code numbers. I have another worksheet tab in the same excel file which contains a list of defect code numbers and defect descriptions. I want to display the defect description in a yellow comment box when I move the cursor over the cell which contains the defect code number. I looked at Excel's comment feature, but it appears that these comment boxes can contain only fixed text. Is there any way to incorporate a lookup function into the comment box so that it can look up ...

Formula for vlookup and then sum
Hello. I need a formula to do a vlookup for whenever a certain cost code/cost type appears in column A, then go to column F and sum the dollar amounts. Help please :) Hi, You can only use full columns in E2007 for E2003 and earlier define a shorter range =SUMPRODUCT((A:A="Your cost code")*(F:F)) Mike "Alberta Rose" wrote: > Hello. I need a formula to do a vlookup for whenever a certain cost > code/cost type appears in column A, then go to column F and sum the dollar > amounts. Help please :) 2003 use array formula { =SUM((A1:A10...

Formula will not calculate
I have a workbook that is not shared or protected, and the formulas I enter do not calculate. If I enter =C2=B2 it displays the formula as typed instead of calculating. There is no apostrophe before the "=" sign either. What's the issue? How can I get my formulas to work? Thanks. EU Never mind. The format of the cell was set to "Text". I reset it to "General" and now the cell will allow the formula to calculate. Thanks. "Excel User" wrote: > I have a workbook that is not shared or protected, and the formulas I enter > do not calculate....

copying formulas #9
I am trying to copy formulas in a spreadsheet that pulls the data from every 4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6, n6). When I try to copy the pattern to the next 4 cells in the row, anticipating r6, v6, etc. instead it repeats as f6, j6, n6, r6. How do I correct this? One way =OFFSET(Sheet2!\$B\$6,,COLUMN(A:A)*4-4) copy across Regards, Peo Sjoblom "Tracey" wrote: > I am trying to copy formulas in a spreadsheet that pulls the data from every > 4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6, > n6). Wh...

Path added to formula during copy
Dear All, I am using Win XP, and Office XP, when I copy one excel file to other disk drive, a path of the source drive had been added to all formula in the cell in the target file, but this is not the way I wanted. How to prevent the automatic source added to the formula ? Thanks. Select the range to copy edit|Replace what: = (equal sign) with: \$\$\$\$\$ replace all Now all the formulas are plain old text. Do the copy|paste Then fix the original range and the pasted range: Select the range edit|replace what: \$\$\$\$\$ with: = replace all And the strings will be converted to formulas. andy wr...

Check to see if a formula is correct
I want to do something like this Sub Macro1() If Range("A1") =(=B1 + B2) Then ' This is a formula Exit Sub Else Range("C1").Select 'Has correct formula Selection.Copy Range("A1").Select Selection.PasteSpecial Paste:=xlFormulas End If End Sub How do I do this? Hi Try Sub Macro1() If Range("A1").HasFormula Then Exit Sub Else Range("C1").Copy Range("A1").PasteSpecial Paste:=xlFormulas Application.CutCopyMode = False ...

Preserving formulas when using sort function
Hi If I have a two columns of data say, A and B and the are added together in rows in Column C eg A1 + B1 = C1 A2 + B2 = C2 etc If I then move the contents of cell A1 and put it in say A23 the value of C1 does not change because the C1 become A23 + B1. However, if I sort Column A then the value of C1 changes and the formula appears to be wrong because it now takes the new value of cell A1 How can I make sure that when the sort takes place that wherever the value in A1 ends up it is still added to B1 to create a value in C1? Any help appreciated Hi Andy, Why aren't you moving th...

in a macro how to make a part of the formula a variable
I am trying to copy a cell from one file into another file in a macro. There are hundreds of files all linking to the same cell so I thought I would set the formula up so that is has a variable for the filename but I can't figure out the syntax. The formula should link to cell \$r\$16 in worksheet "monthly" in the changing file name and the variable I used for the changing file name is budget_file. The following didn't work. Can someone tell me how to do this? ActiveCell.FormulaR1C1 = "=" & budget_file & 'monthly'!\$r\$16&qu...

less than greater than formula required
Hello I would like some help with a formula I require to speed up my analysi of areas. A B C D E F Colums Length Breadth Area M2 <50 50-500 >500 Headings 5 5 25 25 - - 25 25 625 625 20 20 400 400 I would like the areas to appear in categories <50, 50-500 and >500 bu I do not know the formula require. Please help as this would speed up my workload. Thanks Regards Jennif...

A formula that only shows the number of ex: "A" in a column and
Hi! Can i make a formula that only shows the number of ex: "A" in a column and ignore any hidden rows that result from filtering. Maybe i ca combine =SUBTOTAL(3;P:P) and =COUNTIF(\$P:\$P;"=A") in some way? Regards Gunnar Gunnar, You basically need to reproduce your filtering: =SUMPRODUCT((P1:P10="A")*(B1:B10="B")*(C1:C10="C")*1) HTH, Bernie MS Excel MVP "Gunnar Sandstr�m" <gunnar.sandstrom@ericsson.com> wrote in message news:c9n09c\$hh2\$1@newstree.wise.edt.ericsson.se... > Hi! > Can i make a formula that only shows ...

How do I copy formula but only increment certain ranges?
I want to copy a VLOOKUP formula down down the worksheet. When I use the drag handle it increments 2 values ie A1, A2, A3 etc. I want it to increment one of the values but i want the other to stay the same. How do I do it?? Please help Mart A preceding \$ indicates a part of the address to hold constant, as in \$A1, A\$1, or \$A\$1 to hold the column, row, or both constant. Jerry Martc wrote: > I want to copy a VLOOKUP formula down down the worksheet. When I use the drag > handle it increments 2 values ie A1, A2, A3 etc. I want it to increment one > of the values but i want the ...

reference to a formula
Hi I have a workbook that has several formulas and 15 sheets which causes the workbook to be very large. Is there a way to reference to a formula instead of copy and pasting the same formula on every worksheet? thanks On Feb 17, 6:23=A0pm, "Fawn Lagimodiere" <flagimodi...@shaw.ca> wrote: > Hi I have a workbook that has several formulas and 15 sheets which causes > the workbook to be very large. =A0Is there a way to reference to a formul= a > instead of copy and pasting the same formula on every worksheet? > > thanks Group the sheets, select the first she...