SUMPRODUCT - Every other column

**Jabba** · 03-19-2009, 10:37 AM

Hi All,

I'm after some help with the sumproduct formula.

I need to count the number of cells <>0.

My problem is that the cells are not in sequence i.e A1,C1,E1,G1.

Any idea's ?

**DonkeyOte** · 03-19-2009, 10:56 AM

Use:

=SUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=1),--(A1:Z1<>0))

**Jabba** · 03-23-2009, 08:40 AM

Originally Posted by DonkeyOte

Use:

=SUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=1),--(A1:Z1<>0))

Hi DonkeyOte,

Can you please explain how this formula works, as I'm getting some strange results.

Can I assume that the 2 is the column count ?

Thanks

**DonkeyOte** · 03-23-2009, 08:45 AM

The example will Count the instances of "Odd" columns where the value contained within does not equal 0.

In XL columns can be referred to by their column number ie A=1, B=2 as Z=26 and AA = 27 etc... the example simply tests the Remainder of the Column number once divided by a divisor of 2 --- where the remainder is 1 we know the Column is "odd" (ie A,C,E etc...).

For more info on MOD See XL Help.

**Jabba** · 03-23-2009, 11:34 AM

Originally Posted by DonkeyOte

The example will Count the instances of "Odd" columns where the value contained within does not equal 0.

In XL columns can be referred to by their column number ie A=1, B=2 as Z=26 and AA = 27 etc... the example simply tests the Remainder of the Column number once divided by a divisor of 2 --- where the remainder is 1 we know the Column is "odd" (ie A,C,E etc...).

For more info on MOD See XL Help.

Hi DonkeyOte,

Thanks, now I understand, but to throw another spanner in the works, what if the columns were not uniform ? i.e A,G,L,M,N,S etc..

Is there a method where I can select specific cells from specific columns ?

Thanks in advance for any help/advice

**DonkeyOte** · 03-23-2009, 11:55 AM

It will depend on how many you're referring to... you can use a variety of methods in truth but which will be dependent upon the count...

You could use something as basic as:

=SUM(--(N(A1)<>0),--(N(G1)<>0),--(N(S1)<>0))+COUNTIF(L1:N1,"<>0")

ie treat non contiguous ranges slightly differently...

You could use things like ERROR.TYPE & named range, INDIRECT, CHOOSE but I think we'd need more info.

Perhaps the easiest option would be to add an indicator in a blank row to denote those to be validated...?
eg say row 1 had "x" in A, G, L:N, S then you could use the below to count non-zero values in row 2 within those x columns using:

=SUMPRODUCT(--($A$1:$Z$1="x"),--($A2:$Z2<>0))