This is somewhat related to my Countdown Birthday Flag question, however a
bit different, so I chose to generate a "New" question for this...

I have a flag in Column G which identifies a "countdown" to someone's
Birthday:

10-Day(s) Till Birthday-Send Letter
9-Day(s) Till Birthday-Send Letter

The above flag appears between Days 10-1 (not on the actual Birthday
however). In cell G3, I merely want to "count" how many times the flag
appears in Column G, but I'm having difficulty doing this. I tried t hat
which you see below to no avail. In addition, the formula below needs to be
more refined so that the count only takes in the parameters of 10-1 Day(s)
Till Birthday-Send Letter:

=COUNTIF(G4:G3500<=",10-Day(s) Till Birthday-Send Letter")

Any ideas?

Thanks,

Dan

First off, if you didn't see it, I answered your Conditional Formatting
question back in the other thread. I will use the same conditional for this
question too... the dash that appears only in the "Send Letter" flag.

=COUNTIF(G4:G3500,"*-*")

--
Rick (MVP - Excel)


"Danny Boy" wrote in message
...
This is somewhat related to my Countdown Birthday Flag question, however a
bit different, so I chose to generate a "New" question for this...

I have a flag in Column G which identifies a "countdown" to someone's
Birthday:

10-Day(s) Till Birthday-Send Letter
9-Day(s) Till Birthday-Send Letter

The above flag appears between Days 10-1 (not on the actual Birthday
however). In cell G3, I merely want to "count" how many times the flag
appears in Column G, but I'm having difficulty doing this. I tried t hat
which you see below to no avail. In addition, the formula below needs to
be
more refined so that the count only takes in the parameters of 10-1 Day(s)
Till Birthday-Send Letter:

=COUNTIF(G4:G3500<=",10-Day(s) Till Birthday-Send Letter")

Any ideas?

Thanks,

Dan

Rick and Shane:

Thank you very much for all your help. Everything works that you both
suggested, and I believe I clicked "yes" to indicate that on the Discussion
Group. Sorry again for double posting Rick. I know that creates headaches. I
just wasn't thinking prior to having my morning coffee.

Best,

Dan

"Rick Rothstein" wrote:

First off, if you didn't see it, I answered your Conditional Formatting
question back in the other thread. I will use the same conditional for this
question too... the dash that appears only in the "Send Letter" flag.

=COUNTIF(G4:G3500,"*-*")

--
Rick (MVP - Excel)


"Danny Boy" wrote in message
...
This is somewhat related to my Countdown Birthday Flag question, however a
bit different, so I chose to generate a "New" question for this...

I have a flag in Column G which identifies a "countdown" to someone's
Birthday:

10-Day(s) Till Birthday-Send Letter
9-Day(s) Till Birthday-Send Letter

The above flag appears between Days 10-1 (not on the actual Birthday
however). In cell G3, I merely want to "count" how many times the flag
appears in Column G, but I'm having difficulty doing this. I tried t hat
which you see below to no avail. In addition, the formula below needs to
be
more refined so that the count only takes in the parameters of 10-1 Day(s)
Till Birthday-Send Letter:

=COUNTIF(G4:G3500<=",10-Day(s) Till Birthday-Send Letter")

Any ideas?

Thanks,

Dan

Hi,

This might work:

=COUNTIF(G4:G3500,"<")
or more likely
=SUMPRODUCT(--(LEN(G4:G3500)0))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Danny Boy" wrote:

This is somewhat related to my Countdown Birthday Flag question, however a
bit different, so I chose to generate a "New" question for this...

I have a flag in Column G which identifies a "countdown" to someone's
Birthday:

10-Day(s) Till Birthday-Send Letter
9-Day(s) Till Birthday-Send Letter

The above flag appears between Days 10-1 (not on the actual Birthday
however). In cell G3, I merely want to "count" how many times the flag
appears in Column G, but I'm having difficulty doing this. I tried t hat
which you see below to no avail. In addition, the formula below needs to be
more refined so that the count only takes in the parameters of 10-1 Day(s)
Till Birthday-Send Letter:

=COUNTIF(G4:G3500<=",10-Day(s) Till Birthday-Send Letter")

Any ideas?

Thanks,

Dan

The OP is following up from a couple of earlier threads. It was established
in those threads that there is always something in the cell... either the
number of days until a birthday, a Happy Birthday message or (when 10 or
less days remain until the birthday) the "send letter" message. His "send
letter" message is the only entry that will have a dash in it, hence the
formula I proposed elsewhere in this thread.

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
Hi,

This might work:

=COUNTIF(G4:G3500,"<")
or more likely
=SUMPRODUCT(--(LEN(G4:G3500)0))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Danny Boy" wrote:

This is somewhat related to my Countdown Birthday Flag question, however
a
bit different, so I chose to generate a "New" question for this...

I have a flag in Column G which identifies a "countdown" to someone's
Birthday:

10-Day(s) Till Birthday-Send Letter
9-Day(s) Till Birthday-Send Letter

The above flag appears between Days 10-1 (not on the actual Birthday
however). In cell G3, I merely want to "count" how many times the flag
appears in Column G, but I'm having difficulty doing this. I tried t hat
which you see below to no avail. In addition, the formula below needs to
be
more refined so that the count only takes in the parameters of 10-1
Day(s)
Till Birthday-Send Letter:

=COUNTIF(G4:G3500<=",10-Day(s) Till Birthday-Send Letter")

Any ideas?

Thanks,

Dan

Thaks for the help Shane!

The only problem is that I want the count to occur ONLY if the values are
betwewen "1-Day(s) Till Birthday-Send Letter", and "10-Day(s) Till
Birthday-Send Letter". If the value is greater than 10-Day(s), NO counting
should occur. The formuula suggestion you graciously offered, counts for ALL
values greater than 0, as opposed to those restricted to "1-Day(s) through
10-Day(s).

Hope that offers clarification as to what I'm looking for?
Best,

Dan

"Shane Devenshire" wrote:

Hi,

This might work:

=COUNTIF(G4:G3500,"<")
or more likely
=SUMPRODUCT(--(LEN(G4:G3500)0))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Danny Boy" wrote:

This is somewhat related to my Countdown Birthday Flag question, however a
bit different, so I chose to generate a "New" question for this...

I have a flag in Column G which identifies a "countdown" to someone's
Birthday:

10-Day(s) Till Birthday-Send Letter
9-Day(s) Till Birthday-Send Letter

The above flag appears between Days 10-1 (not on the actual Birthday
however). In cell G3, I merely want to "count" how many times the flag
appears in Column G, but I'm having difficulty doing this. I tried t hat
which you see below to no avail. In addition, the formula below needs to be
more refined so that the count only takes in the parameters of 10-1 Day(s)
Till Birthday-Send Letter:

=COUNTIF(G4:G3500<=",10-Day(s) Till Birthday-Send Letter")

Any ideas?

Thanks,

Dan

Did you see either one of my responses to you (one here in this thread and
one in your previous thread)???

--
Rick (MVP - Excel)


"Danny Boy" wrote in message
...
Thaks for the help Shane!

The only problem is that I want the count to occur ONLY if the values are
betwewen "1-Day(s) Till Birthday-Send Letter", and "10-Day(s) Till
Birthday-Send Letter". If the value is greater than 10-Day(s), NO counting
should occur. The formuula suggestion you graciously offered, counts for
ALL
values greater than 0, as opposed to those restricted to "1-Day(s) through
10-Day(s).

Hope that offers clarification as to what I'm looking for?
Best,

Dan

"Shane Devenshire" wrote:

Hi,

This might work:

=COUNTIF(G4:G3500,"<")
or more likely
=SUMPRODUCT(--(LEN(G4:G3500)0))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Danny Boy" wrote:

This is somewhat related to my Countdown Birthday Flag question,
however a
bit different, so I chose to generate a "New" question for this...

I have a flag in Column G which identifies a "countdown" to someone's
Birthday:

10-Day(s) Till Birthday-Send Letter
9-Day(s) Till Birthday-Send Letter

The above flag appears between Days 10-1 (not on the actual Birthday
however). In cell G3, I merely want to "count" how many times the flag
appears in Column G, but I'm having difficulty doing this. I tried t
hat
which you see below to no avail. In addition, the formula below needs
to be
more refined so that the count only takes in the parameters of 10-1
Day(s)
Till Birthday-Send Letter:

=COUNTIF(G4:G3500<=",10-Day(s) Till Birthday-Send Letter")

Any ideas?

Thanks,

Dan

Hi,

I see, ok, try this

=SUMPRODUCT(--(VALUE(SUBSTITUTE(LEFT(G1:G12,2),"-",""))<=10))

Or use the first formula and change you flag so that it only displays when
the number of days is <=10
=IF(your days formula<=10,your days formula,"")&" your text"

Of course I don't know exactly which formula you ended up using for your
formula, but if you use a variation similar to above you can use the original
sumproduct formula I gave in the previous post.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Danny Boy" wrote:

Thaks for the help Shane!

The only problem is that I want the count to occur ONLY if the values are
betwewen "1-Day(s) Till Birthday-Send Letter", and "10-Day(s) Till
Birthday-Send Letter". If the value is greater than 10-Day(s), NO counting
should occur. The formuula suggestion you graciously offered, counts for ALL
values greater than 0, as opposed to those restricted to "1-Day(s) through
10-Day(s).

Hope that offers clarification as to what I'm looking for?
Best,

Dan

"Shane Devenshire" wrote:

Hi,

This might work:

=COUNTIF(G4:G3500,"<")
or more likely
=SUMPRODUCT(--(LEN(G4:G3500)0))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Danny Boy" wrote:

This is somewhat related to my Countdown Birthday Flag question, however a
bit different, so I chose to generate a "New" question for this...

I have a flag in Column G which identifies a "countdown" to someone's
Birthday:

10-Day(s) Till Birthday-Send Letter
9-Day(s) Till Birthday-Send Letter

The above flag appears between Days 10-1 (not on the actual Birthday
however). In cell G3, I merely want to "count" how many times the flag
appears in Column G, but I'm having difficulty doing this. I tried t hat
which you see below to no avail. In addition, the formula below needs to be
more refined so that the count only takes in the parameters of 10-1 Day(s)
Till Birthday-Send Letter:

=COUNTIF(G4:G3500<=",10-Day(s) Till Birthday-Send Letter")

Any ideas?

Thanks,

Dan

Hi,

I failed to mention the formula I gave you requires that all the cells in
the range contain entries, if not we need to enhance it still further with an
array formula of the form

=SUM(--(IF(ISNUMBER(VALUE(SUBSTITUTE(LEFT(G1:G12,2),"-",""))),VALUE(SUBSTITUTE(LEFT(G1:G12,2),"-","")),"")<=10))

To enter this as an array press Ctrl+Shift+Enter, not Enter.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Danny Boy" wrote:

Thaks for the help Shane!

The only problem is that I want the count to occur ONLY if the values are
betwewen "1-Day(s) Till Birthday-Send Letter", and "10-Day(s) Till
Birthday-Send Letter". If the value is greater than 10-Day(s), NO counting
should occur. The formuula suggestion you graciously offered, counts for ALL
values greater than 0, as opposed to those restricted to "1-Day(s) through
10-Day(s).

Hope that offers clarification as to what I'm looking for?
Best,

Dan

"Shane Devenshire" wrote:

Hi,

This might work:

=COUNTIF(G4:G3500,"<")
or more likely
=SUMPRODUCT(--(LEN(G4:G3500)0))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Danny Boy" wrote:

This is somewhat related to my Countdown Birthday Flag question, however a
bit different, so I chose to generate a "New" question for this...

I have a flag in Column G which identifies a "countdown" to someone's
Birthday:

10-Day(s) Till Birthday-Send Letter
9-Day(s) Till Birthday-Send Letter

The above flag appears between Days 10-1 (not on the actual Birthday
however). In cell G3, I merely want to "count" how many times the flag
appears in Column G, but I'm having difficulty doing this. I tried t hat
which you see below to no avail. In addition, the formula below needs to be
more refined so that the count only takes in the parameters of 10-1 Day(s)
Till Birthday-Send Letter:

=COUNTIF(G4:G3500<=",10-Day(s) Till Birthday-Send Letter")

Any ideas?

Thanks,

Dan

Hi,

I see one more contingency you may need to address - what if the number of
days is above 99. Then

=SUM(--(IF(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(LEFT(G1:G 12,3),"-",""),"D",""))),VALUE(SUBSTITUTE(SUBSTITUTE(LEFT(G 1:G12,3),"-",""),"D","")),"")<=10))

Again Array entered.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Danny Boy" wrote:

Thaks for the help Shane!

The only problem is that I want the count to occur ONLY if the values are
betwewen "1-Day(s) Till Birthday-Send Letter", and "10-Day(s) Till
Birthday-Send Letter". If the value is greater than 10-Day(s), NO counting
should occur. The formuula suggestion you graciously offered, counts for ALL
values greater than 0, as opposed to those restricted to "1-Day(s) through
10-Day(s).

Hope that offers clarification as to what I'm looking for?
Best,

Dan

"Shane Devenshire" wrote:

Hi,

This might work:

=COUNTIF(G4:G3500,"<")
or more likely
=SUMPRODUCT(--(LEN(G4:G3500)0))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Danny Boy" wrote:

This is somewhat related to my Countdown Birthday Flag question, however a
bit different, so I chose to generate a "New" question for this...

I have a flag in Column G which identifies a "countdown" to someone's
Birthday:

10-Day(s) Till Birthday-Send Letter
9-Day(s) Till Birthday-Send Letter

The above flag appears between Days 10-1 (not on the actual Birthday
however). In cell G3, I merely want to "count" how many times the flag
appears in Column G, but I'm having difficulty doing this. I tried t hat
which you see below to no avail. In addition, the formula below needs to be
more refined so that the count only takes in the parameters of 10-1 Day(s)
Till Birthday-Send Letter:

=COUNTIF(G4:G3500<=",10-Day(s) Till Birthday-Send Letter")

Any ideas?

Thanks,

Dan