Counting Question!

  • Thread starter Thread starter Danny Boy
  • Start date Start date
D

Danny Boy

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,"*-*")
 
Hi,

This might work:

=COUNTIF(G4:G3500,"<>")
or more likely
=SUMPRODUCT(--(LEN(G4:G3500)>0))
 
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.
 
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
 
Did you see either one of my responses to you (one here in this thread and
one in your previous thread)???
 
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.
 
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.
 
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:G12,3),"-",""),"D",""))),VALUE(SUBSTITUTE(SUBSTITUTE(LEFT(G1:G12,3),"-",""),"D","")),"")<=10))

Again Array entered.
 
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
 
Back
Top