Ignoring cells with zero value or blank

**Moshsoft** · 04-24-2009, 06:08 AM

I have the following formula in a spread sheet, when the destination is blank it adds the related cell.

Can I amend the formula to ignore zero's or blank cells?

{((=SUM(IF(Tracking!$F$4:$F$65536=C15,Tracking!$N$4:$N$65536,0))}

**DonkeyOte** · 04-24-2009, 06:26 AM

Per my other note on your other thread ... avoid arrays where possible, certainly avoid using arrays with large ranges (& generally accepted it's better to use Sumproduct than SUM(IF CSE as it's more robust)

Re: your formula, there's no need to use an Array, a standard SUMIF would suffice (single condition):

=SUMIF(Tracking!$F$4:$F$65536,C15,Tracking!$N$4:$N$65536)

Limiting the range here is still a good practice to get into but not as important as when dealing with Arrays.

**Moshsoft** · 04-25-2009, 03:37 AM

Originally Posted by DonkeyOte

Per my other note on your other thread ... avoid arrays where possible, certainly avoid using arrays with large ranges (& generally accepted it's better to use Sumproduct than SUM(IF CSE as it's more robust)

Re: your formula, there's no need to use an Array, a standard SUMIF would suffice (single condition):

=SUMIF(Tracking!$F$4:$F$65536,C15,Tracking!$N$4:$N$65536)

Limiting the range here is still a good practice to get into but not as important as when dealing with Arrays.

Hey DonkeyOte, (sweet name btw)

I've tried your formula without success so far, I'm not sure that it should or I might be misunderstanding the use/difference between the sumif & array formula...

If you look at my other post, I have attached a dummy sample of a spead sheet I've built...

http://www.excelforum.com/excel-work...ml#post2082016