Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count only if another column has data in Pivot Table? | Excel Worksheet Functions | |||
HOW DO I COUNT CELLS WITH DATA BY COLUMN? | Excel Worksheet Functions | |||
I need to count data in one colum based on data in another column | Excel Worksheet Functions | |||
adjacent data count from a binary column | Excel Worksheet Functions | |||
How do I count or display unique data in a column? | Excel Worksheet Functions |