Sum Cells Ignoring Blank Cells with Formulas

Preccor

New Member
Joined
Jul 11, 2019
Messages
25
Office Version
  1. 365
Platform
  1. Windows
=IF(OR(I2="x"),"2.5","") generates the value 2.5 in cell Q2
=IF(OR(J2="x"),"2.5","") generates the value 2.5 in cell R2
=IF(OR(K2="x"),"2.5","") generates the value 2.5 in cell S2
=IF(OR(L2="x"),"2.5","") generates the value 2.5 in cell T2
=IF(OR(M2="x"),"2.5","") generates the value 2.5 in cell U2
=IF(OR(N2="x"),"2.5","") generates the value 2.5 in cell V2
=IF(OR(O2="x"),"2.5","") generates the value 2.5 in cell W2

I would like to sum total the values in cells Q2:W2 when "X" is not entered in any of the cells in the range I2:O2.

For example ... there is no "X" in cell O2. This renders an error value in G2. [See Image #3]
(Image #2: Sum total works fine which is illustrated in #1)

Question: What is the formula required in cell G2 to sum total the cells in range Q2:W2 when an "X" is missing in any of the cells in range I2:O2?

Image attached for illustration.
 

Attachments

  • Capture1.jpg
    Capture1.jpg
    77.6 KB · Views: 10
  • Capture2.jpg
    Capture2.jpg
    106.6 KB · Views: 11
  • Capture 3.jpg
    Capture 3.jpg
    86 KB · Views: 10

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
For example ... there is no "X" in cell O2. This renders an error value in G2.
If you used a SUM formula, the same as the one in G11 then you wouldn't encounter this problem.
 
Upvote 0
Jasonb75 - Yes that is correct. However, when an "X" in any of the cells in range I2:O2 is removed an error occurs in G2. Is there a formula to avoid the error?
 
Upvote 0
Please have a look to see if this addresses your question:
MrExcel_20200613.xlsx
GHIJKLMNOPQRSTUVW
1Your ScoreMonTueWedThuFriSatSun
215xxxxxx2.52.52.52.52.52.5 
Sheet3
Cell Formulas
RangeFormula
G2G2=SUM(Q2:W2)
Q2:W2Q2=IF(I2="x",2.5,"")
 
Upvote 0
Awesome. It was a problem with the formulas in the range cells. Thank you very much. I really appreciate you finding the solution. Warm regards.
 
Upvote 0
You're welcome...glad to help. I don't think you needed the OR condition, or inserting the numeric value as text (i.e., the quotes around 2.5).
 
Upvote 0
Jasonb75 - Yes that is correct. However, when an "X" in any of the cells in range I2:O2 is removed an error occurs in G2. Is there a formula to avoid the error?
As I said,
If you used a SUM formula, the same as the one in G11 then you wouldn't encounter this problem.
=SUM(Q2:W2) will not cause an error.

The error is caused by using + symbols with cells that contain "" to show a blank. If you use SUM then you don't need to use the + symbols in the formula.
 
Upvote 0
Thanks for clarifying that point, Jason...I forgot to mention it. I think there were three issues: 1) the + vs. SUM issue with "" blanks (the actual error generator), 2) using "2.5" rather than 2.5 (which will be treated as 0 since it is text, so an incorrect sum would be obtained), and 3) using IF(OR(ref="x") which is an unnecessarily complex way to test for ref="x".
 
Upvote 0
Thanks, Kirk. I didn't actually look at the formulas in Q:W, only the one in G in the second screen capture.

Having the numbers formatted as text in the formula would explain why SUM wasn't being used.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top