Setting a limit on a countif function

[mdoolin14]

I want an error message to pop up once my COUNTIF function reaches a count over 30. Here is my function =COUNTIF(G:G, "10:30:00 AM"). Once there are 31 "10:30" values in column G I want the error message to appear. I've tried a few things in Data Validation but am not quite sure what I need to do.


Any help would be great

Thanks,
Matt

[Steve0492]

Try:

IF(COUNTIF(G:G, "10:30:00 AM")>30,Error,COUNTIF(G:G, "10:30:00 AM"))

Let me know if this works.

Steve

[Steve0492]

IF(COUNTIF(G:G, "10:30:00 AM")>30,"ERROR",COUNTIF(G:G, "10:30:00 AM"))

Sorry, bad syntax on the previous formula.

[FDibbins]

ok, "30" is not the same as 10:"30". 10:30 is just the format the mumber os displayed as. its actual value is 0.4375, and 10:30 is actually 0.438194444444444.

So if you want to restrict your count to 10:30 (which im guessing is not the ONLY number you want it to check for), you need to resstrict it to <0.438194444444444

not sure of the exact format, but it needs to include a test for =MINUTE(10:30)+0

[mdoolin14]

Steve,

That's closer to what I was wanting. It switches from 30 to error if another 10:30 value is added but I was hoping for some sort of pop up dialog box so it would stop the person from entering more values.

[Steve0492]

Ok, in that case I am nearly certain that you will have to use VBA. Creating a Message Box with VBA is simple, but I am not sure how to have it constantly checking if there are more than 30 "10:30"s. Perhaps someone else can help with this.

Steve

[Cutter]

You can use Data Validation for the cells in column G. Use this formula after selecting Custom: =COUNTIF(G:G,0.4375)<31
Then set up your error message on the Error Alert tab. You can also use an input message as an option.

[shg]

Or =COUNTIF(G:G, "10:30")<31

[mdoolin14]

That worked! Thank You. Now is there anyway to expand on this to include a limit on multiple values such as =countif(G:G, "10:30, 11:00, 11:30, 2:00, 2:30, 3:00")<31. Would this work to make sure each value wouldn't exceed 30?

[mdoolin14]

To be more clear I have about 300 rows of data and in column G, you can input time slots 10:30, 11:00. 11:30, 2:00, 2:30, or 3:00. If any individual value exceeds 30 in the column, not over 30 total inputs, I want an error message to pop up.

[shg]

=COUNTIF(G:G, myCellAddress)<31