COUNTIF & Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Lipetz - 16 Dec 2005 19:35 GMT

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

Reply to this Message

Peo Sjoblom - 16 Dec 2005 19:37 GMT

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

Signature

Regards,

Peo Sjoblom

> 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,
[quoted text clipped - 17 lines]
> Thanks,
> David

Reply to this Message

David Lipetz - 16 Dec 2005 19:48 GMT

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.

> Try
>
[quoted text clipped - 25 lines]
>> Thanks,
>> David

Reply to this Message

Peo Sjoblom - 16 Dec 2005 20:09 GMT

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

Signature

Regards,

Peo Sjoblom

> Thanks Peo! That worked, but now I have another problem.
>
[quoted text clipped - 3 lines]
>
> > 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
> >
[quoted text clipped - 21 lines]
> >> Thanks,
> >> David

Reply to this Message

David Lipetz - 16 Dec 2005 21:11 GMT

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)

>I am not sure I understand, but maybe
>
[quoted text clipped - 42 lines]
>> >> Thanks,
>> >> David

Reply to this Message

David Lipetz - 16 Dec 2005 19:41 GMT

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

> 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,
[quoted text clipped - 16 lines]
> Thanks,
> David

Reply to this Message