Counting two numbers

V

vijaydsk1970

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.
 
D

Domenic

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!
 
V

vijaydsk1970

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
 
D

Domenic

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!
 
Top