Need help with COUNTIF function - with specific criteria search

Hi,

I have a question in relation to the COUNTIF function.

I have a spreadsheet with 20 columns - starting at A1 to T1 - and ending at A100 toT100. I have successfully found out how to use the COUNTIF function to 'search' all of these from A1/T1 to A100/T100 and tell me how many times the number 21 appears across that range of cells.

Now I want to expand on this COUNTIF function.

Is it possible to define the COUNTIF function to identify how many times other numbers appear only when the number 21 appears.


Example:

On my spreadsheet I have used the COUNTIF function to identify that the number 21 has appeared 15 times. Now I want to know how to do either of the following:

Either:

a) list the number of times other numbers have appeared with the number 21 on the same column i.e. 8 has appeared 2 times, 11 has appeared 6 times, etc - but only listing numbers when the have appeared with the number 21 in the column - but listing all number -say from 1 to 100, or

b)List the number of times a specific number has appeared on a column when 21 is also on that same column i.e. how many times has the number 6 appeared in the spreadsheet where the number 21 appears on a column, I could then replicate this formula for other numbers

Also

If this is possible, can this formula/function be expanded to a third or fourth number i.e. can the function/formula identify how many times the number 6 has appeared on a column when both of the numbers 21 and 18 have also appeared?

Any help/ideas/suggestions gratefully appreciated.

R

Hi Ron,

Thanks for the response.

I have attached a sample workbook.

I have manually included what I would like to do/see to explain.

Regards

Hi Ron,

Thanks for the prompt response.

That looks like the sort of thing that I need.

I can definately work with it and will give it a try.

Much appreciated.

Regards,

Robbert