What are some of the specific criteria for Formula One?

Countif multiple criteria and range

• ... I need to implement a COUNTIF formula where one of the criteria is true only if it falls within a RANGE. Example: COUNTIF A:A = "ABC" COUNTIF B:B = "PHONE" COUNTIF C:C is within the range of 3500 <> 7000 I have been trying variations of SUMPRODUCT and I just can't figure out how to add the range check.

• Answer:

  Robert Since your using xl2003, the limitation in xl2003 and earlier is that you can not address an entire column in an array formula. Many people feel sumproduct is not an array formula since it doesn't require using array entry (ctrl+shift+enter rather than just enter) to commit the formula. However, I won't get into that debate. Suffice it to say that you can address up to one cell short of an entire column; A1:A65535 instead of A:A or A1:A65536 as an example. That said, the more cells you address, the longer it will take to calculate. So it is always best to address as few cells as you need to or will need to address. Hope that adds some clarity. A:A isn't a valid address for sumproduct unless you are using xl2007 and later. If you are using xl2007, then you might as well use COUNTIFS (new function introduced in Excel 2007 to handle countif with multiple conditions) =countifs(A:A,"ABC",B:B,"PHONE",C:C,">=3500")-countifs(A:A,"ABC",B:B,"PHONE",C:C,">7000") the equivalent Sumproduct formula is: =SUMPRODUCT(--(A:A="ABC"),--(B:B="PHONE"),--(C:C>=3500),--(C:C =3500),--(C1:C100<=7000))