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

I have a spreadsheet with a lot of columns. However, I want to only count
data in every other column. Help please.

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

Can you give an example of what you want? Do you mean COUNT or SUM?

"Mechelle" wrote:

I have a spreadsheet with a lot of columns. However, I want to only count
data in every other column. Help please.

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

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

"Mechelle" wrote:

I have a spreadsheet with a lot of columns. However, I want to only count
data in every other column. Help please.

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

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.

"Toppers" wrote:

Can you give an example of what you want? Do you mean COUNT or SUM?

"Mechelle" wrote:

I have a spreadsheet with a lot of columns. However, I want to only count
data in every other column. Help please.

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

Using the first row in this example

=SUMPRODUCT(--(MOD(COLUMN(1:1),2)=1),--(1:10))

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

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


I assumed the numbers cannot be negative


--
Regards,

Peo Sjoblom



"Mechelle" wrote in message
...
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.

"Toppers" wrote:

Can you give an example of what you want? Do you mean COUNT or SUM?

"Mechelle" wrote:

I have a spreadsheet with a lot of columns. However, I want to only
count
data in every other column. Help please.

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

=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)=1),--(A2:M20))

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)

"Mechelle" wrote in message
...
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.

"Toppers" wrote:

Can you give an example of what you want? Do you mean COUNT or SUM?

"Mechelle" wrote:

I have a spreadsheet with a lot of columns. However, I want to only
count
data in every other column. Help please.

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

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


"Mechelle" wrote in message
...
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.

"Toppers" wrote:

Can you give an example of what you want? Do you mean COUNT or SUM?

"Mechelle" wrote:

I have a spreadsheet with a lot of columns. However, I want to only
count
data in every other column. Help please.