Sumproduct with a range and CountIf criteria

1. 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!

2. Re: Sumproduct with a range and CountIf criteria

Try

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

3. Re: Sumproduct with a range and CountIf criteria

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

4. Re: Sumproduct with a range and CountIf criteria

=COUNTIFS(B1:B12,D4,BO1:BO12,">=1",BO1:BO12,"<=20")
or
=SUMPRODUCT(--(BO1:BO12>=1),--(BO1:BO12<=20),--(B1:B12=D4))

5. Re: Sumproduct with a range and CountIf criteria

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.

6. Re: Sumproduct with a range and CountIf criteria

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

7. Re: Sumproduct with a range and CountIf criteria

Originally Posted by mhynson

I have a sheet called outstanding that has column BO with numbers 1-100 in it!
If those are the only possibilities for column BO then:

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

There are currently 1 users browsing this thread. (0 members and 1 guests)