Desktop

Ask something
Discussion Locked

simple excel problem

0 helpful
6 replies

by salfordmv

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Can any brilliant person out there tell me how to count how many cells in a column in an Excel spreadsheet have values that are equal to or greater than 90 and under 200?

I've already used COUNTIF but it will only allow me to have one criterion and not both...
Thanks!
Gerry,
Sunny Salford,
Manchester
UK.

6 replies

by john_wills

Knowing very little Excel, I propose a low-level solution. Suppose your critical column is X. Set up 3 new columns. col1=iif(X>90,1,0); col2=iif(x<200,1,0); col3=col1 * col2; Count col3.

by leehellcat

To do this quickly. Select the heading of the column you want to count. Go to 'Data' then 'Filter' then 'AutoFilter'.

Then click on the arrow in the heading for that column and choose 'Customs' from the drop down.
Set your parameters (so greaterthat 90 and less than 200).

Then it will only show these results. At this point, go to the cell a couple down from the bottom of the list, and goto 'Insert', then 'Function' Choose 'COUNTA' and select the cells in the column that have values in (except the header row).

Should work fine. Only point is that when you are doing the custom filter you can only pick numbers from your list, so if there is no 90 or 200 in it you will have to pick the nearest number.

by leehellcat

Use the Autofilter option to filter a column between 2 set results, and then do a COUNTA for the results

This discussion is closed to new replies.