Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |