2

I have two cells in the same column adjacent to each other (Q7, Q8). They are used in multiple formulas as range.
eg:
1. SUM(Q1:Q10)
2. SUM(E6:N13)-SUM(P6:AJ13)

How do i skip those two cells from being computed in the formula without making any changes to the formula. I want the value in the cell to be displayed but should not be used in computations.

1

Since you are using the SUM() function, there is one other option. SUM() ignores text values, so you could enter the values in Q7 and Q8 as text. Format the cells as Text and enter the numbers with a leading single quote character. In the screenshot below, Excel shows the green warning triangle for numbers stored as text, but the SUM() function only calculates the real numbers.

enter image description here

That approach will also work if Q7 and Q8 contain formulas that return numbers. You simply wrap a TEXT() formula around the existing formula to format the result as text

=TEXT(<yourFormula>,"0")

Select the desired number format.

  • This is exactly what i was looking for, thank you – KraZy Jul 24 '16 at 1:56
2

You can't omit these cells from being included in the formula without making changes in the formula.

You can't have your cake and eat it.

I suggest you change the formula to exclude the cell explicitly, like

  1. SUM(Q1:Q10)-sum(q7,q8)

and

  1. SUM(E6:N13)-SUM(P6:AJ13)-sum(q7,q8)
2

If you know that you might want to exclude certain items from the formula, you can prepare for this as follows:

  • in R1 through R10 enter 1
  • use the formula: =SUMPRODUCT((R1:R10)*(Q1:Q10))

enter image description here

Now if you want to exclude Q7 and Q8, just set R7 and R8 to zero.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.