Thread: Exclude Zero Value Cells in a Sumproduct Formula calculating every other column

Are you sure?

I tested it just now and got results with 0's and blanks in the columns...

Do you have an error in one of those columns.

Originally Posted by Weezy

They might also check to see if they have a number that is actually text which looks like a number. If so then they could get an error like that.

Weezy, the above assertion is not correct.

In this particular calculation a #DIV/0! could only result from an underlying error value.
(there are no division calculations taking place within the SUMPRODUCT itself)

The "Numbers stored as Text" error (assuming explicit coercion) would generally* generate #VALUE! errors (as opposed to #DIV/0!)
*unless other errors are encountered first thus taking precedence

If you can't correct for underlying errors at source (ie handling the #DIV/0!) then you can use an Array rather than SUMPRODUCT, ie:

=SUM(IF(MOD(COLUMN(B12:CK12),2)*ISNUMBER(B12:CK12),IF((B12:CK12>0),B12:CK12)))
confirmed with CTRL + SHIFT + ENTER

However, accounting for underlying errors at source is generally advised.