MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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