| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting two numbers
i need to post 2 dates in a single cell (eg. 15 & 20) and at the end of that
row there is a count formula covering this cell. How to post the dates in the single cell, so that the count formula counts 2 dates there is no scope to post in next cell. thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting two numbers
Assuming that B2 contains 15 & 20, and G2 contains the count formula,
try the following... 1) Select G2 2) Insert Name Define Name: Result Refers to: =EVALUATE("{"&SUBSTITUTE($B2,"&",",")&"}") 3) Enter the following formula in G2... =COUNT(Result) To include another range with this count, try... =COUNT(Result,Range) Hope this helps! In article , vijaydsk1970 wrote: i need to post 2 dates in a single cell (eg. 15 & 20) and at the end of that row there is a count formula covering this cell. How to post the dates in the single cell, so that the count formula counts 2 dates there is no scope to post in next cell. thanks in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting two numbers
Dear Domenic
thanks for your suggestion. i could able to get first part of your advice. In the 2nd part could you explain a little bit further I am trying that in a sheet looks like this. apr may jun ..... cum prod1 prod2 prod1 prod2 prod1 prod2 prod1 prod2 15 &20 20 20 12 10 3 =count(a1,c1,e1,...) thanks in advance "Domenic" wrote: Assuming that B2 contains 15 & 20, and G2 contains the count formula, try the following... 1) Select G2 2) Insert Name Define Name: Result Refers to: =EVALUATE("{"&SUBSTITUTE($B2,"&",",")&"}") 3) Enter the following formula in G2... =COUNT(Result) To include another range with this count, try... =COUNT(Result,Range) Hope this helps! In article , vijaydsk1970 wrote: i need to post 2 dates in a single cell (eg. 15 & 20) and at the end of that row there is a count formula covering this cell. How to post the dates in the single cell, so that the count formula counts 2 dates there is no scope to post in next cell. thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting two numbers
I'm a little unclear, but let's assume that A1:E1 contains your data...
If you want to count A1, B1, C1, D1, and E1, try... =SUMPRODUCT(--(A1:E1<""),LEN(A1:E1)-LEN(SUBSTITUTE(A1:E1,"&",""))+1) If you want to count every 2nd column (A1, C1, E1), try... =SUMPRODUCT(--(A1:E1<""),--(MOD(COLUMN(A1:E1)-COLUMN(A1),2)=0),LEN(A1:E1 )-LEN(SUBSTITUTE(A1:E1,"&",""))+1) Hope this helps! In article , vijaydsk1970 wrote: Dear Domenic thanks for your suggestion. i could able to get first part of your advice. In the 2nd part could you explain a little bit further I am trying that in a sheet looks like this. apr may jun ..... cum prod1 prod2 prod1 prod2 prod1 prod2 prod1 prod2 15 &20 20 20 12 10 3 =count(a1,c1,e1,...) thanks in advance |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| counting numbers | Excel Discussion (Misc queries) | |||
| Counting Numbers | Excel Discussion (Misc queries) | |||
| counting if two columns contain the same numbers | Excel Worksheet Functions | |||
| counting numbers | Excel Discussion (Misc queries) | |||
| Counting numbers | Excel Discussion (Misc queries) | |||
Hybrid Mode
