Interesting COUNTIF Question...

mikeprice53

Active Member
Joined
Jan 21, 2003
Messages
297
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 ?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

mikeprice53

Active Member
Joined
Jan 21, 2003
Messages
297
:)

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 ;)
 
Upvote 0

Forum statistics

Threads
1,187,179
Messages
5,962,057
Members
438,578
Latest member
MrJimC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top