Interesting COUNTIF Question...

[mikeprice53]

Can anybody help me out on this please, In one of my projects I have made a full VBA Project holiday calendar and I thought it was all fully functional until this very morning when I realised if a cell has multiple data entries a countif will not see my required data...

The formula I am using is:

=COUNTIF($A$1:$G$20,$A$25 & " (1/2am) ")

whereby cell A25 on the sheet will be the Current Users Login name specified with Environment settings from VBA.

Hence if cell A25 = "Fred smith" my search will count the number of times Fred has booked a holiday.

HERES THE PROBLEM THOUGH...

If a cell in the RANGE that I am searching for matches value of "Fred Smith (1/2am) " then it will tell me correctly

BUT

If for instance in that same cell there is another users holiday already booked in whether it be before or after my search criteria it will not count it as a match for COUNTIF...

If a range cell had for instance "Fred Smith (1/2am) John Williams (1/2am) " it dosnt count anything for it

To say the least I am a little bit miffed as this has been a fun VBA project for me teaching me quite a bit and to learn it is failing to work on such a small pain in the rear end search is annoying

can anyone think of a way around this please... Thank you

P.S. To summarise this the lookup of the cell needs to be searching for a partial match not a whole cell match but is this possible ?

 

[Brian from Maui]

=COUNTIF($A$1:$G$20,$A$25 & " (1/2am) ")

Try

=COUNTIF($A$1:$G$20,"*"&$A$25&"*")

 

[mikeprice53]

thanks very much Brian I adapted what you put to this and now works ( as I have option for Full day , 1/2am and 1/2pm days off

=COUNTIF($A$1:$G$20,"*"&$A$25&" (Full Day) "&"*")

and so on

thank you once again