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.


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


If you so desire, you can also make use of a single formula.

Array-enter in a cell:


In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER).



Posted by Chris Taylor on June 18, 2001 1:32 AM

The SUMIF function is probably what you are looking for.


Posted by Martin on June 18, 2001 1:37 AM


I think the best formula to use would be a 'sumif' formula. Your formula would like like this

Posted by Aladin Akyurek on June 18, 2001 4:56 AM

Wasn't fully awake I guess. :)