Sum if not blank

M

Marcy

C23=IF(ISBLANK('Merch Spec'!S3),"",('Merch Spec'!S3)
D23 is blan
E23= + sign (formatted as general
F23 is blan
G23==IF(ISBLANK('Merch Spec'!T3),"",('Merch Spec'!T3)
H23 is blan
I23==IF(ISBLANK('Merch Spec'!U3),"",('Merch Spec'!U3)
J23 has an = sign (formatted as general

I would like K23 to total C23+G23+I23 (formatted as currency) but would like it to be blank (or "-") if those three cells are not populated. However, when I used =If(isblank(C23,G23,I23),"-",sum(C23:I23)) I still get $0.00 as the result
How can I correct this, please
Thanks for your help
 
P

Paul

Marcy said:
C23=IF(ISBLANK('Merch Spec'!S3),"",('Merch Spec'!S3))
D23 is blank
E23= + sign (formatted as general)
F23 is blank
G23==IF(ISBLANK('Merch Spec'!T3),"",('Merch Spec'!T3))
H23 is blank
I23==IF(ISBLANK('Merch Spec'!U3),"",('Merch Spec'!U3))
J23 has an = sign (formatted as general)

I would like K23 to total C23+G23+I23 (formatted as currency) but would
like it to be blank (or "-") if those three cells are not populated.
However, when I used =If(isblank(C23,G23,I23),"-",sum(C23:I23)) I still get
$0.00 as the result.
How can I correct this, please?
Thanks for your help!

Try this:
=IF(OR(C23="",G23="",I23=""),"-",SUM(C23:I23))
 
B

Bob Phillips

Marcy,

I think Paul meant =IF(AND( not =IF(OR(

An alternative is

=IF(COUNTA(C23,G23,I23)=0,"-",SUM(C23:I23))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

Marcy

Well, this worked fine for the cells that are blank BUT
C25 has this:
IF(ISBLANK('Merch Spec'!S5),"",('Merch Spec'!S5)) which is now populated with $3.45

But, the total in K25 shows as - (as if the result should be $0.00) but in fact, the total should be $3.45 as the other two cells to be totaled (G25+ I25) are blank

Can you tell me where I went wrong? Please.
 
M

Marcy

Thanks to both..
The solution offered by Paul and slightly amended by Bob did the trick just perfectly

I did try the alternate suggestion using the COUNTA argument but just so you know, that returned a value of $0.00 even when the cells were blank (C23,G23,I23)

But, no matter...as I said the +IF(AND...) got the job done
Thanks so much!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top