Comparing values in a column (2000/SR-1)

Topic

New Reply

I have a column of numbers between .25 and 100. At the end of the column I would like to have a count of all the numbers in the column between 1 and 75. Is there a formula I can use that will give me this count?

I know I can create a separate column that returns either true or false depending on the value of the cells in question, then count just the true values. But I want to work with a single cell at the end of each column.

Any suggestions?

Reply | Quote

Replies

If you really mean between (not including 1 and 75), then the following array formula (hold down Ctrl and Shift when you press Enter) will do what you want:

=SUM((A1:A17>1)*(A1:A17<75))

If you want to include ones and 75s, then use this formula:

=SUM((A1:A17>=1)*(A1:A17<=75))

Change the range A1:A17 to the range where your values are located.

Reply | Quote

Can you tell me why this formula works? I tested it and it’s perfect!

(BTW, I was looking for a “between” statement).

Reply | Quote

It works because the a logical True from a comparison is represented by a value of 1 and False is represented by 0. Therefore, whenever either or both comparisons is False, one or both of the values will be 0 and the product will be 0. Only when both conditions are a True will both results be 1 and the product will be 1. The array formula creates an array of ones and zeros for each comparison, and the product of those two arrays is an array of ones when both comparisons are True and zero when either or both are False. The SUM function adds up those ones and zeros to get the count of then cells that meet both conditions.

Reply | Quote

Lets say that column A houses the numbers of interest, E1 the lowest criterion (your .75), and E2 the highest criterion (your 1).

=COUNTIF(A:A,”>”&E1)-COUNTIF(A:A,”>=”&E2)

will give you a count of numbers in A which are > .75 and < 1, thus excluding the numbers that are equal to the criteria. A bit tweaking will allow you to modify the formula for counting that is based on a different form of between.

This formula is "cheaper" than an equivalent array or SUMPRODUCT formula which are definitely needed when you want a multiconditional count or sum.

Aladin

Reply | Quote

I populated cells A1 thru A34 with values ranging from .75 to 100. Using your formula, I came up with a count of 8 when there are really 24 cells that meet the criteria I’m looking for. I was looking for a formula that would count all numbers falling between the ranges mentioned.

Reply | Quote

The rule of applying COUNTIF when we have a between condition may appear confusing. It just requires some exercise. As I tried to state in my first reply, a multiconditional count or sum is ordinarily computed by means of an array formula or a SUMPRODUCT formula that is capable of operating on arrays (By the way, PivotTables etc can also be used). As a side note, array formulas are more generic than the SUMPRODUCT formulas. The former requires the key combination control+shift+enter, while the latter is entered as an ordinary formula.

COUNTIF and SUMIF, by design, cannot work with more than one condition/criteria. With a “between” condition (which boils down to multiple conditions), COUNTIF can still be used for counting (and SUMIF for summing/totaling).

Now back to your question.

Ambiguity of between (as Legare also noted):

Lets say that E1 houses the lowest and E2 the highest of criteria.

What do we mean when we say “between E1 and E2″? It can be one of the following:

> E1 and = E1 and E1 and = E1 and =”&E1)-COUNTIF(A:A,”>”&E2)

Note. Column A should not have anything else (any other numbers) but the numbers of interest that we want count.

How this formula works?

The first COUNTIF counts every cell in A that is greater than or equal to the value in E1. The second COUNTIF counts every cell in A that is greater than the value in E2. The latter count is then subtracted from the former count, producing the desired result.

The array and SUMPRODUCT versions are — these formulas by design won’t accept whole columns as range specifiers, so exact ranges must be provided:

{=SUM((A1:A34>=E1)*(A1:A34=E1)*(A1:A34<=E2)) [ normally entered ]

Aladin

Reply | Quote

Please ignore my post below. It was too late when I replied and I misread what Aladin’s formula was doing.

I believe his formula should have been:

=COUNTIF(A:A,">="&E1)-COUNTIF(A:A,"<="&E2)

In addition, E1 should contain 1 and E2 should contain 75 not .75 and 1 as he stated.

Reply | Quote