IF MULTIPE CELLS ARE BLANK, DISPLAY MINUS SIGN. Else, SUM.

J

JRC23

New Member
Joined
Jun 13, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

Newbie here.
Need some assistance for a formula below. I'm trying to eliminate the zero value on Q5 in case the values of K5, M5, AND O5 are blanks.
And instead, display minus sign (if the 3 cells are zero or blank). Else, if it has values on it, SUM(K5, M5, O5).


Formula should be placed in Q5:
I tried =IF(ISBLANK(K5),M5*O5,"-",SUM(K5,M5,O5)) but it's error :)

IF K5, M5, O5 are blank, then display "-", else SUM(K5,M5,O5)
excel forum.JPG



Thank you in advance.

JRC23
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
if( counta(K5, M5, O5)=0, "-" , SUM(K5, M5, O5) )

you say if blank - but then go onto say
display minus sign (if the 3 cells are zero or blank).
Click to expand...

if( SUM(K5, M5, O5)<=0, "-" , SUM(K5, M5, O5) )

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.
www.mrexcel.com

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
www.mrexcel.com www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
 
Upvote 0
Solution
etaf said:
if( counta(K5, M5, O5)=0, "-" , SUM(K5, M5, O5) )

you say if blank - but then go onto say


if( SUM(K5, M5, O5)<=0, "-" , SUM(K5, M5, O5) )

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.
www.mrexcel.com

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
www.mrexcel.com www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
Click to expand...
Here is the google drive link.

Excel Forum - Google Drive

drive.google.com drive.google.com
 
Upvote 0
etaf said:
if( counta(K5, M5, O5)=0, "-" , SUM(K5, M5, O5) )

you say if blank - but then go onto say


if( SUM(K5, M5, O5)<=0, "-" , SUM(K5, M5, O5) )

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.
www.mrexcel.com

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
www.mrexcel.com www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
Click to expand...
Hi ETAF,

This one WORKED. =IF(COUNTA(K5,M5,O5)=0,"-",SUM(K5,M5,O5))

Thank you very BIG!!
 
Upvote 0
You are welcome

EXCEL FORUM.xlsx
ABCDEFGHIJKLMNOPQR
1DATE
2ccwwyyttlttl544454ttl 5ttl 4
3SLF %SLF %SLF %SLF %SLF %SLF %C + W + YC + W + YCCWWYYC + W + YC + W + Y
4Fri2-Jun70.59%70.59%--101.47%99.41%100.00%98.03%1212344337356349
5Wed7-Jun-------0.00%-0
6Thu8-Jun-------0.00%-0
7Fri30-Jun-------0.00%-0
FINAL LOADS 2023
Cell Formulas
RangeFormula
C4:D7C4=IF(ISBLANK(K4),"-",SUM(K4/17))
E4:F7E4=IF(ISBLANK(M4),"-",SUM(M4/33))
G4:H7G4=IF(ISBLANK(O4),"-",SUM(O4/339))
I4:J7I4=IF(Q4="-","-", Q4/356)
Q4:Q7Q4=IF(COUNTA(K4,M4,O4)=0,"-",SUM(K4,M4,O4))
R4:R7R4=SUM(L4,N4,P4)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D7Cell Value="-"textNO
D7Cell Value>=0.3001textNO
D7Cell Valuebetween 0 and 30.01%textNO
D5Cell Value="-"textNO
D5Cell Value>=0.3001textNO
D5Cell Valuebetween 0 and 30.01%textNO
D4Cell Value="-"textNO
D4Cell Value>=0.3001textNO
D4Cell Valuebetween 0 and 30.01%textNO
C7Cell Value="-"textNO
C7Cell Value>=0.3001textNO
C7Cell Valuebetween 0 and 30.01%textNO
H7Cell Value="-"textNO
H7Cell Value>=0.3001textNO
H7Cell Valuebetween 0 and 30.01%textNO
H5Cell Value="-"textNO
H5Cell Value>=0.3001textNO
H5Cell Valuebetween 0 and 30.01%textNO
H4Cell Value="-"textNO
H4Cell Value>=0.3001textNO
H4Cell Valuebetween 0 and 30.01%textNO
G7Cell Value="-"textNO
G7Cell Value>=0.3001textNO
G7Cell Valuebetween 0 and 30.01%textNO
G5Cell Value="-"textNO
G5Cell Value>=0.3001textNO
G5Cell Valuebetween 0 and 30.01%textNO
G4Cell Value="-"textNO
G4Cell Value>=0.3001textNO
G4Cell Valuebetween 0 and 30.01%textNO
I7Cell Value="-"textNO
I7Cell Value>=0.3001textNO
I7Cell Valuebetween 0 and 30.01%textNO
I5Cell Value="-"textNO
I5Cell Value>=0.3001textNO
I5Cell Valuebetween 0 and 30.01%textNO
I4Cell Value="-"textNO
I4Cell Value>=0.3001textNO
I4Cell Valuebetween 0 and 30.01%textNO
J7Cell Value="-"textNO
J7Cell Value>=0.3001textNO
J7Cell Valuebetween 0 and 30.01%textNO
J5Cell Value="-"textNO
J5Cell Value>=0.3001textNO
J5Cell Valuebetween 0 and 30.01%textNO
J4Cell Value="-"textNO
J4Cell Value>=0.3001textNO
J4Cell Valuebetween 0 and 30.01%textNO
C5Cell Value="-"textNO
C5Cell Value>=0.3001textNO
C5Cell Valuebetween 0 and 30.01%textNO
C4Cell Value="-"textNO
C4Cell Value>=0.3001textNO
C4Cell Valuebetween 0 and 30.01%textNO
J6Cell Value="-"textNO
J6Cell Value>=0.3001textNO
J6Cell Valuebetween 0 and 30.01%textNO
I6Cell Value="-"textNO
I6Cell Value>=0.3001textNO
I6Cell Valuebetween 0 and 30.01%textNO
H6Cell Value="-"textNO
H6Cell Value>=0.3001textNO
H6Cell Valuebetween 0 and 30.01%textNO
G6Cell Value="-"textNO
G6Cell Value>=0.3001textNO
G6Cell Valuebetween 0 and 30.01%textNO
D6Cell Value="-"textNO
D6Cell Value>=0.3001textNO
D6Cell Valuebetween 0 and 30.01%textNO
C6Cell Value="-"textNO
C6Cell Value>=0.3001textNO
C6Cell Valuebetween 0 and 30.01%textNO



Using Sum - which will display a - if zero

EXCEL FORUM.xlsx
ABCDEFGHIJKLMNOPQR
1DATE
2ccwwyyttlttl544454ttl 5ttl 4
3SLF %SLF %SLF %SLF %SLF %SLF %C + W + YC + W + YCCWWYYC + W + YC + W + Y
4Fri2-Jun70.59%70.59%--101.47%99.41%100.00%98.03%1212344337356349
5Wed7-Jun-------0.00%-0
6Thu8-Jun-------0.00%-0
7Fri30-Jun-------0.00%-0
FINAL LOADS 2023
Cell Formulas
RangeFormula
C4:D7C4=IF(ISBLANK(K4),"-",SUM(K4/17))
E4:F7E4=IF(ISBLANK(M4),"-",SUM(M4/33))
G4:H7G4=IF(ISBLANK(O4),"-",SUM(O4/339))
I4:J7I4=IF(Q4="-","-", Q4/356)
Q4:Q7Q4=IF( SUM(K4, M4, O4)<=0, "-", SUM(K4, M4, O4) )
R4:R7R4=SUM(L4,N4,P4)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D7Cell Value="-"textNO
D7Cell Value>=0.3001textNO
D7Cell Valuebetween 0 and 30.01%textNO
D5Cell Value="-"textNO
D5Cell Value>=0.3001textNO
D5Cell Valuebetween 0 and 30.01%textNO
D4Cell Value="-"textNO
D4Cell Value>=0.3001textNO
D4Cell Valuebetween 0 and 30.01%textNO
C7Cell Value="-"textNO
C7Cell Value>=0.3001textNO
C7Cell Valuebetween 0 and 30.01%textNO
H7Cell Value="-"textNO
H7Cell Value>=0.3001textNO
H7Cell Valuebetween 0 and 30.01%textNO
H5Cell Value="-"textNO
H5Cell Value>=0.3001textNO
H5Cell Valuebetween 0 and 30.01%textNO
H4Cell Value="-"textNO
H4Cell Value>=0.3001textNO
H4Cell Valuebetween 0 and 30.01%textNO
G7Cell Value="-"textNO
G7Cell Value>=0.3001textNO
G7Cell Valuebetween 0 and 30.01%textNO
G5Cell Value="-"textNO
G5Cell Value>=0.3001textNO
G5Cell Valuebetween 0 and 30.01%textNO
G4Cell Value="-"textNO
G4Cell Value>=0.3001textNO
G4Cell Valuebetween 0 and 30.01%textNO
I7Cell Value="-"textNO
I7Cell Value>=0.3001textNO
I7Cell Valuebetween 0 and 30.01%textNO
I5Cell Value="-"textNO
I5Cell Value>=0.3001textNO
I5Cell Valuebetween 0 and 30.01%textNO
I4Cell Value="-"textNO
I4Cell Value>=0.3001textNO
I4Cell Valuebetween 0 and 30.01%textNO
J7Cell Value="-"textNO
J7Cell Value>=0.3001textNO
J7Cell Valuebetween 0 and 30.01%textNO
J5Cell Value="-"textNO
J5Cell Value>=0.3001textNO
J5Cell Valuebetween 0 and 30.01%textNO
J4Cell Value="-"textNO
J4Cell Value>=0.3001textNO
J4Cell Valuebetween 0 and 30.01%textNO
C5Cell Value="-"textNO
C5Cell Value>=0.3001textNO
C5Cell Valuebetween 0 and 30.01%textNO
C4Cell Value="-"textNO
C4Cell Value>=0.3001textNO
C4Cell Valuebetween 0 and 30.01%textNO
J6Cell Value="-"textNO
J6Cell Value>=0.3001textNO
J6Cell Valuebetween 0 and 30.01%textNO
I6Cell Value="-"textNO
I6Cell Value>=0.3001textNO
I6Cell Valuebetween 0 and 30.01%textNO
H6Cell Value="-"textNO
H6Cell Value>=0.3001textNO
H6Cell Valuebetween 0 and 30.01%textNO
G6Cell Value="-"textNO
G6Cell Value>=0.3001textNO
G6Cell Valuebetween 0 and 30.01%textNO
D6Cell Value="-"textNO
D6Cell Value>=0.3001textNO
D6Cell Valuebetween 0 and 30.01%textNO
C6Cell Value="-"textNO
C6Cell Value>=0.3001textNO
C6Cell Valuebetween 0 and 30.01%textNO
 
Upvote 1
etaf,
With your solution/formula, it even help me to auto eliminate the conditional format color (red) on Column I. I will do the same formula for Column R5 and it will automatically affect the Column J to white color if it has no value, which I really NEEDED as well. :cool:

Again, THANK YOU VERY BIG for the help! Really appreciate it.

b excel forum.JPG
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Help to sum all cells above until blank cell
Replies
3
Views
207
RemyMoo
R
E
  • Question
Estimating Spreadsheet-Summing
Replies
1
Views
146
Peter_SSs
Peter_SSs
E
  • Solved
Summing Values
Replies
3
Views
226
LazyBug
LazyBug
jski21
  • Question
Find Differences in Two Column Sets and Report Result
Replies
0
Views
381
jski21
jski21
himderfella
  • Solved
Do not calculate formula if there are blank cells
Replies
4
Views
538
MARK858
MARK858

Forum statistics

Threads
1,215,161
Messages
6,123,363
Members
449,097
Latest member
thnirmitha

Share this page

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