Count the number of specific values in a cell

[Guest]

I am using one cell to enter a list of dates. i.e.
3/9 3/11 4/27 4/28 5/4 5/5 5/9

I would like to count the number of times 3/ or 4/ ... occurs in this cell.

I read Bob Phillips post about using
=SUMPRODUCT(--(MONTH(A1:A20)=2)
or
=SUMPRODUCT(--(TEXT(A1:A20,"mmm")="Feb"))
but I am not sure how to use this. the "mmm" especially confuses me.

Thanks,

 

[Guest]

To be quite honest, using one cell to store these values in and then trying
to extract information is not a very good design. It makes it unnecessary
complicated.
Assume that A1 holds the values

=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,"3/","")))/LEN("3/"),(LEN(A1)-LEN(SUBSTITUTE(A1,"4/","")))/LEN("4/"))



Regards,

Peo Sjoblom

 

[Ron Rosenfeld]

I am using one cell to enter a list of dates. i.e.
3/9 3/11 4/27 4/28 5/4 5/5 5/9

I would like to count the number of times 3/ or 4/ ... occurs in this cell.

I read Bob Phillips post about using
=SUMPRODUCT(--(MONTH(A1:A20)=2)
or
=SUMPRODUCT(--(TEXT(A1:A20,"mmm")="Feb"))
but I am not sure how to use this. the "mmm" especially confuses me.

Thanks,

=LEN(A6)*2-SUM(LEN(SUBSTITUTE(A6,{"3/","4/"},"/")))

However, it would be simpler if you stored your dates in separate cells.

The above formula will give incorrect answers if your are looking for Jan, Feb,
Nov and/or Dec and all of these months are in the string. So if that were to
be the case, perhaps a formula like:

=OR(LEFT(A6,1)={"3","4"})+LEN(A6)*2-SUM(LEN(SUBSTITUTE(A6,{" 3/"," 4/"}," /")))

should be used.

Note that LEN(A6) has to be multiplied by the number of strings for which you
are looking. In the above example, the multiplier is two.


--ron

 

[Guest]

I was brought in on a project at work, and realize it would be easier if it
were in separate cells. I was just extracting data from a spreadsheet
already in use. Thanks for your help though

 

[Ron Rosenfeld]

I was brought in on a project at work, and realize it would be easier if it
were in separate cells. I was just extracting data from a spreadsheet
already in use. Thanks for your help though

Hopefully, my second formula will be useful for you.


--ron