COUNTIF & Criteria

[David Lipetz]

I'm using the SUMIF function to add numbers in a range that meet a given
criteria. The criteria I need to use is a range of numbers (>=1 and <=5, >=6
and <=10, etc).

While I have been able to perform this calculation by hard coding in the
parameters (">=1" and ">5" for example), I am unable to complete the formula
if I want to use cell references for the criteria.

Here is the whole formula that currently works by hard coding in the
criteria:

=COUNTIF(Revised!$A$2:$A$336,">=1")-COUNTIF(Revised!$A$2:$A$336,">5")

In other words, I want to use >=G2 rather than >=1 and use >I2 rather than

5.

How do I accomplish this?

Thanks,
David

 

[Peo Sjoblom]

Try

=COUNTIF(Revised!$A$2:$A$336,">="&G2)-COUNTIF(Revised!$A$2:$A$336,">"&I2)

otherwise excel sees it as you are looking for the text G2 and I2

 

[David Lipetz]

Correction to my post:
I am using COUNTIF not SUMIF.

 

[David Lipetz]

Thanks Peo! That worked, but now I have another problem.

I wanted to create this as an array formula so that the criteria is selected
from each row: G2 & I2, G3 & I3, and so forth. With the & in front of the
cell reference, it uses that same reference throughout the array.

 

[Peo Sjoblom]

I am not sure I understand, but maybe

=SUM(COUNTIF(Revised!$A$2:$A$336,">="&G2:G336))-SUM(COUNTIF(Revised!$A$2:$A$
336,">"&I2:I336))

needs to be array entered with ctrl + shift & enter

 

[David Lipetz]

Thanks again! The actual formula is:
=SUMIF(Revised!$A$2:$A$335,">="&A2:A8,Revised!$L$2:$L$335)-SUMIF(Revised!$A$2:$A$335,">"&C2:C8,Revised!$L$2:$L$335)