Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

EXCEL FORMULA

Posted on 2011-02-17
4
Medium Priority
?
261 Views
Last Modified: 2012-05-11
Hi i would like some assistance with the following formula. What im trying to do is;
if the value of cells I33:I48 = zero or nothing, leave blank, otherwise sum cells I33:I48

=IF(SUMPRODUCT(I33:I48)="","",SUMPRODUCT(I33:I48))
0
Comment
Question by:Frank .S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 500 total points
ID: 34921487
Hello,

maybe

=if(sum(I33:I48)=0,"",sum(I33:I48))

or

=if(count(I33:I48)=0,"",sum(I33:I48))

The first one will return a blank even if the cells contain a 0, the second one will return  a blank only if all cells are empty.

cheers, teylyn
0
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 34922208
Frank,
I would use the Sum option.  The value will be zero for any combination of cells that are blank, null, or 0.
But, I think the formula should be:  =if(Sum(I33:I48)=0,"",SUMPRODUCT(I33:I48))

I am curious as to what a SUMPRODUCT answer provides for a range of numbers?
0
 

Author Comment

by:Frank .S
ID: 34922694
hI dodahd, thanks i'll try your solution also.
0
 
LVL 50
ID: 34922777
Sumproduct() with just one range parameter produces the same result as Sum(). Sumproduct() is a very powerful function, but in this scenario I don't see how it would be beneficial.
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question