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

0
a_bolland (26)
5/10/2005 7:47:22 PM
excel.misc 78881 articles. 5 followers. Follow

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

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 <1115754442.204059.112950@f14g2000cwb.googlegroups.com>,
 "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
domenic22 (716)
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
HuwDavies (8)
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 
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 <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)
> 
> Adjust the ranges accordingly.
> 
> Hope this helps!
> 
> In article <1115754442.204059.112950@f14g2000cwb.googlegroups.com>,
>  "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
domenic22 (716)
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
a_bolland (26)
5/10/2005 9:49:41 PM
Reply:

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

additional formula ?
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...

Comments with Lookup Formulas??
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...