Sumproduct with a range and CountIf criteria

Hi I have not been able to find an answer elswhere so I figured I would post the question for some help.

I have a sheet called outstanding that has column BO with numbers 1-100 in it, I have another column in that sheet B with Customers. I would like to first check to see if the customer matches and then if it falls within a certain range and if so count it.

Example Sumproduct(Outstanding!BO:BO>=1)*(Outstanding!BO:BO<=20)If(Outstanding!B:B=D4)

In the example above I would like to find anything that falls between 1 and 20 in column BO as long as it matches the customer name (D4) in column B:B.

Any help could be greatly appreciated!

Try

Sumproduct((Outstanding!BO:BO>=1)*(Outstanding!BO:BO<=20)*(Outstanding!B:B=D4))

It's not a good idea to use full-column references with SUMPRODUCT - try COUNTIFS instead:

=COUNTIFS(Outstanding!BO:BO,">=1",Outstanding!BO:BO,"<=20",Outstanding!B:B,D4)

Hope this helps.

Pete

Thank you this worked perfectly! also thank you for the heads up on the full column reference, that may be why I have been having performance issues.

Glad to help. COUNTIFS (and the other xxxIFx functions) is clever enough to only use the used range in its calculations, whereas SP and other array formulae will examine every cell in the range, used or not (and there are 1 million + in each column !!)

Pete