# Interesting COUNTIF Question...

#### mikeprice53

##### Active Member
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

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

Replies
5
Views
171
Replies
3
Views
168
Replies
5
Views
236
Replies
5
Views
249
Replies
5
Views
137

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.

### Which adblocker are you using?

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

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