How do I use the Count function for data in every other column?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet with a lot of columns. However, I want to only count
data in every other column. Help please.
 
thsi sounds like a question witth many answers

A offset inside a for loop works

Myoffset = 0
Mytotal = 0
for OffsetCount = 0 to 10 step 2
Mytotal = range("A1"). _
offset(Rowoffset:=0,columnoffset:=OffsetCount)
next offsetcount
 
I want to count if there are entries in every other column excluding zeros.

i.e.
A B C D
Paid Reserve Paid Reserve
0 10,000 500 0

I want a count (not sum) of those fields in colum A and C only, and if "0"
is the entry, I don't want to count it.

So in the example above, the result should be 1.
 
Using the first row in this example

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

of course if you don't want text to be included

=SUMPRODUCT(--(MOD(COLUMN(1:1),2)=1),--(ISNUMBER(1:1)),--(1:1>0))


I assumed the numbers cannot be negative
 
=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)=1),--(A2:M2>0))

just change A2:M2 to your actual columns. I have also assumed you don't want
to count any negatives, hence > 0

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
As long as your range starts in an "odd" column (A, C, E, etc.), and as long
as every cell in the range is filled in with a value (0 or a number, not
empty), this formula should do what you want

=SUMPRODUCT((MOD(COLUMN(A1:D1),2)=1)*(A1:D1<>0))

where the range starts in Column A as you requested. If, by the way, you
ever want to do this and your range will start in an "even" column (B, D, F,
etc.), then just change the =1 to =0. The above formula ends in column D,
but you can expand that to whatever ending column you want... it is only the
beginning column for the range that must be accounted for.

Rick
 
Back
Top