Need help with COUNTIF function across more than 1 column

[robbertl]

Hi,

I have a question I hope somebody can help me with.

How do I setup a COUNTIF function so that it 'searches/uses' more than one column for results.

Example:
I have a spreadsheet with 20 columns - starting at A1 to T1 - and ending at A100 toT100. I want 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.

This COUNTIF function will reference data on a sheet called 50 - so at the moment my current single colum COUNTIF looks like this -=COUNTIF('50'!A1:A100,21) - and it works fine for one colum, but I need to search 20 columns.

Any idea how I can do this?

Thanks,

R

 

[Vidar]

Hi Robbert and welcome to the board
Try this one:
=SUMPRODUCT(--('50'!A1:T100=21))

 

[JoeMo]

Try:

=COUNTIF('50'!A1:T100,21)

 

[robbertl]

Hi,

Many thanks for the feedback... I will give that a try.

I have one other question as per my original question...

Can I expand this function, or use any other function, to achieve the following?

Lets say I have successfully used the COUNTIF function to identify the number of time 21 appears, is it then possible to identify how many times the number 18 (for example) has appeared when 21 appears - so that I would get the result = 21 appears 10 times over the 100 columns, and of those 10 times the number 18 has appeared on the same column with 21, say 5 times.

If this is possible, can this be expanded to a third or fourth number i.e.

Can the function identify how many times the number 6 has appeared on a column when both of the numbers 21 and 18 have also appeared?

Again, any assistance is appreciated.

R

 

[JoeMo]

Just use the function in another cell and change 21 to 18 and so on ...