Sum Formula
Posted by Karen on June 17, 2001 6:29 PM
I am working on an overtime expenditure sheet, and I am having a problem with a sum formula. Certain cells in column (G10:G29) will have an "X" in them. What I need to do is sum only the cells of column (N10:N29) that correspond to the cells in column (G10:G29) that have the "X" in them. For example, cells (G12, G16, G19 and G25) have the "X" in them. I want to sum cells (N12,N16, N19 and N25). However, the cells with the "X" in them will change from week to week. Therefore I want to do a formula that will calculate only the cells in column (N10:N29) that correspond to the cells in column (G10:G29) that have the "X" in them. I hope this isn't too confusing! Thanks in advance for any help in this formula.
Karen
Posted by Larry Stoy on June 17, 2001 7:23 PM
Go to an unused column. Go to Row 10 of that unused column. Type in the formula "=IF(G10="X",N10,0)". Then, go to Row 30 of that same column and type in the regular sum formula for that range. This will allow you to total only the cells in N10:N29 that have a corresponding "X" in G10:G29.
Posted by Aladin Akyurek on June 17, 2001 11:30 PM
Karen
If you so desire, you can also make use of a single formula.
Array-enter in a cell:
=SUM((G10:G29)="X")*(N10:N29)
In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER).
Aladin
===================
Posted by Chris Taylor on June 18, 2001 1:32 AM
The SUMIF function is probably what you are looking for.
SUMIF(G10:G29,"x",N10:N29)
Posted by Martin on June 18, 2001 1:37 AM
Karen,
I think the best formula to use would be a 'sumif' formula. Your formula would like like this
=SUMIF(g1:g10,"=x",n1:n10)
Posted by Aladin Akyurek on June 18, 2001 4:56 AM
Wasn't fully awake I guess. :)
