Solved

EXCEL FORMULA

Posted on 2011-02-17
4
225 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

912 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

21 Experts available now in Live!

Get 1:1 Help Now