Countif if counted results are based on formula

[tmax]

I have the birthdays of all employees in column L.
I have the number of days remaining until the birthday is coming up in
Column M

the formula is
=IF(TEXT(L12,"mmdd")=TEXT(TODAY(),"mmdd"),"",((TEXT(L12,"d-mmm-")&(YEAR(NOW())+(TEXT(L12,"mmdd")<TEXT(NOW(),"mmdd"))))-TODAY())&IF(((TEXT(L12,"d-mmm-")&(YEAR(NOW())+(TEXT(L12,"mmdd")<TEXT(NOW(),"mmdd"))))-TODAY())<=10,"",""))

now I want to count all the birthdays that are coming up within the next 10
days, but if I use the countif formula it simply ignores the formula based
results.

=COUNTIF(M:M,"<=10") if I "type" the same numbers it does count those.

L M
04/01/82 7
04/03/82 9
03/26/82 1
03/28/85 3

Does anyone have a suggestion?

 

[Stefi]

Try this way:
=COUNTIF(M:M,"<="&10)

Regards,
Stefi

„tmax†ezt írta:

 

[Ashish Mathur]

Hi,

Try

=COUNTIF(1*M:M,"<=10")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[tmax]

Sorry, neither one works. Any other suggestions?

 

[Ashish Mathur]

Hi,

Sorry about my previous past - that will not work. Try this array formula
(Ctrl+Shoft+Enter)

=COUNT(IF((1*M10:M13<10),M10:M13*1))

You may also try this non-array formula

=SUMPRODUCT(1*(1*(M10:M13)<10))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[tmax]

The first one did not work, the second one kind of worked, but did also count
the empty cells, which I forgot to mention where there.

So here is how i adjusted it and it works, thank you ver much!!! For your
help!!!


=SUMPRODUCT(1*(1*(P$10$1006)>0.4),1*(1*(P$10$1006)<10))

 

[Ashish Mathur]

Hi,

Glad I could help

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com