Replies

Excellent thanks for the pointer, can this also be accomplished by merely entering the formula for this task in a cell below the column in question?

Thanks again…
Marty

Good Morning Andrew and RG,

Wow, ask and you shall receive…..both will do the job. As usual this site exceeds expectations, an excellent learning tool for all. I’m coming to the VBA table a little late but is powerful stuff

Thanks Again,
Marty

Thank you Stephane and sdckapr,

this may take me a bit to digest, between classes. Yes cells such as 1, 4, 7, 10 can be ignored as they refer to shift…they are n/a for this purpose. Let me try this input out…..thanks for your input and patience with a formual “green horn”

Regards,
Marty

I do not think I was totally clear either. Attached is a clearer representation of what I am trying to do…..count the frequency of numbers in a specified cell range.

Hopefully this helps out.

Thank You for for patience and assistance….

Regards,
Marty

Hi to All,

A number will be counted if it is separated by a comma , or a hyphen -. All the numbers pertaining to or contained in the date cells can be ignored, such as cells A1, A4, A7 etc.

Thank you for your help.

Regards,
Marty

Hi To All,

Sorry for not being more explicit..let me try to explain. If I have a column in excel a range say A1:A220 with some cells containing a series of numbers separated by commas, and in other rows or cells I have some text along with numbers, and I wish to tally how many times within this cell range that for example the number 8 appears of the number 256 appears. The results of this search or query could be placed in one or several cells on the same worksheet.

hopefully this helps…

Thanks again…

Marty

Hi Catharine,

Thanks for the detail…..I got it, and the absolute cell references make it easier to work with.

Thanks again.

Regards,
Marty

Thank you for your responses, Catharine what does the ),D23,””) portion do? How is the copying of the data to the adjacent column performed, and how would this formula be different if the data where copied to say cloumn M?

Great suugestion regarding the test criteria in one location ..

Thanks,
Marty

Well funny how it doesn’t work unless one hits the “upload file” icon…sorry. Here it is.

Thank you for your help…

Regards,
Marty

Hi,

I should have been a little more detailed, I’ve attached a section of the worksheet problem. From the sample i need to evaluate the data in column “d” ex. d420.5, any data that do not meet this test must be copied to an adjacent column such as column f or g. Column f or g would contain in the (same row number) only the actual data that failed the above test. Hope this is a little clearer.

Thank you..

Marty

Hi Hans,

Thank you for the explanation….the more I learn about excel formulas etc, the more I like it…..thanks again.

Regards,
Marty

Good Morning or Afternoon Paul,

A little help…ABS returns the absolute value, but what does the (A1-3)>0.5)^1 do? I get the >= part, I am looking for values that are 2.53.5, in which case a 1 is returned, if not a 0.

Many Thanks……

Marty

Good Morning Jerry,

Im a little slow on the response, but thank you for the background. I’ve been a long time and it is a indespensible site….the MS Excel help function and MS blogs fall far short of a good explanation when it comes to logic and VBA.

Thanks again

Regards,
Marty

Catharine, HansV, & Andrew,

Thank you for your input. I was using the wrong statement. The distinction between COUNTBLANK and COUNTA is now clear. Thank you again for your assistance.

Regards,
Marty

Hi Catharine and HansV,

This is an extension to the original post. While using the “COUNTA and COUNTBLANK” functions to count the number of cells within the same column that contain, a 1, a 0 or are blank, the COUNTBLANK function returns a correct value, those cells that are blank. Yet the COUNTA function merely counts the total number of cells within the specified range, whether a 1, 0 or blank.

Any suggestions? I’ve followed the sparse excel help window w/ no success.

Many Thanks for your help…

Regards,
Marty