Count ??
 

I am trying to count random cells (non contiguous) in the same column and
show (in one cell) the total number that are 1, =0, <1

my formula thus far =COUNT(C41,C61,C81,C101,C121,C141,C161,C181)
this shows how many numbers (8)

How do I distinguish for my criteria above?

Your thoughts are appreciated.

Count ??
 

Not very sophisticated but it works
=(ABS(C41)<=1)+(ABS(C61)<=1)+(ABS(C81)<=1)+(ABS(C1 01)<=1).....
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"dakotablu" wrote in message
...
I am trying to count random cells (non contiguous) in the same column and
show (in one cell) the total number that are 1, =0, <1

my formula thus far =COUNT(C41,C61,C81,C101,C121,C141,C161,C181)
this shows how many numbers (8)

How do I distinguish for my criteria above?

Your thoughts are appreciated.

Count ??
 

Thanks Bernard

So, is there no way I can count through the selected cells to the amount of
values that are 0, =0, <0



"Bernard Liengme" wrote:

Not very sophisticated but it works
=(ABS(C41)<=1)+(ABS(C61)<=1)+(ABS(C81)<=1)+(ABS(C1 01)<=1).....
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"dakotablu" wrote in message
...
I am trying to count random cells (non contiguous) in the same column and
show (in one cell) the total number that are 1, =0, <1

my formula thus far =COUNT(C41,C61,C81,C101,C121,C141,C161,C181)
this shows how many numbers (8)

How do I distinguish for my criteria above?

Your thoughts are appreciated.

Count ??
 

You can Sum logical test

less than one
=SUM( (C41<1), (C61<1), ... )

equal to zero
=SUM( (C41=0), (C61=0), ... )

greater than one
=SUM( (C411), (C611), ... )

If wanted, the results can be cancatenated into one cell with text.

="Count <1 " & SUM( (C41<1), (C61<1), ... ) & ", Count =0 " & SUM( (C41=0),
(C61=0), ... ) & ", Count 1 " & SUM( (C411), (C611), ... )


"dakotablu" wrote:

I am trying to count random cells (non contiguous) in the same column and
show (in one cell) the total number that are 1, =0, <1

my formula thus far =COUNT(C41,C61,C81,C101,C121,C141,C161,C181)
this shows how many numbers (8)

How do I distinguish for my criteria above?

Your thoughts are appreciated.

Count ??
 

I am trying to count random cells (non contiguous)
=COUNT(C41,C61,C81,C101,C121,C141,C161,C181)

Your cells follow a pattern, every 20 rows, so you can use a formula like
this:

=SUMPRODUCT(--(MOD(ROW(C41:C181)-ROW(C41),20)=0),--(C41:C1810))

For =0, an empty cell evaluates as 0 so if you have empty cells and don't
want them counted:

=SUMPRODUCT(--(MOD(ROW(C41:C181)-ROW(C41),20)=0),--(ISNUMBER(C41:C181)),--(C41:C181=0))

--
Biff
Microsoft Excel MVP


"dakotablu" wrote in message
...
Thanks Bernard

So, is there no way I can count through the selected cells to the amount
of
values that are 0, =0, <0



"Bernard Liengme" wrote:

Not very sophisticated but it works
=(ABS(C41)<=1)+(ABS(C61)<=1)+(ABS(C81)<=1)+(ABS(C1 01)<=1).....
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"dakotablu" wrote in message
...
I am trying to count random cells (non contiguous) in the same column
and
show (in one cell) the total number that are 1, =0, <1

my formula thus far =COUNT(C41,C61,C81,C101,C121,C141,C161,C181)
this shows how many numbers (8)

How do I distinguish for my criteria above?

Your thoughts are appreciated.