CountIF with conditions, match names and count if < or > 1000

**dans123** · 04-01-2012, 01:41 AM

Hi all,

after a formula that will match a name in sheet 2, to the database on sheet 1 and then count only the values that is less than 1000 that corresponds to that persons name.

Not sure if this is possible but any help would be appreciated.

Thanks.

**DonkeyOte** · 04-01-2012, 02:41 AM

If as implied you are using XL2003

Sheet2!B1:
=SUMPRODUCT((Sheet1!$A$1:$A$100=A1)*(Sheet1!$B$1:$B$100<1000))
where A1 holds Name

If using XL2007+ see COUNTIFS function

Above assumes dimensions of both ranges (name & values) are identical

**MUHAMMAD IMRAN** · 04-01-2012, 02:43 AM

The easiest way is to add column in "Sheet 1" with the condition to pick all values >1000 and then use countif formula in Sheet 2 from that NEW column. There are other solutions too but need some time.

**dans123** · 04-01-2012, 07:54 AM

Thanks DonkeyOte, that worked!

just a follow up question, if i wanted the formula to be greater than -1000 and less than 1000 and another to be less than -1000 and greater than 1000, h ow would i go about doing that? I have just added >1000 and <1000 but it hasnt seemed to work.

Thanks.