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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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 ;)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,096
Messages
5,768,068
Members
425,451
Latest member
JohnBrooksBiddle

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