Solved

EXCEL FORMULA

Posted on 2011-02-17
4
232 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:FrankSasso
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 125 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 18

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:FrankSasso
ID: 34922694
hI dodahd, thanks i'll try your solution also.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
modify formula same cell multiple times 15 28
Excel Formula 5 43
Excel Formula to split product code 5 13
conditional formatting 4 41
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

813 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now