# count with multiple criteria

#### TinaP

##### Well-known Member
I'm trying to count the number of loans we have according to status and processor. The status (1,2,3,4) is in column S and the processors initials are in column J. For instance I would like to know how many loans we have with status 3 being processed by ABC.

I tried using sumproduct, but I'm not very experienced with it, so I didn't do very well. I'd rather not use array formulas since the person I'm doing this for has been know to modify formulas and I know he won't remember CTRL+ALT+ENTER.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### ExcelChampion

##### Well-known Member
=SUMPRODUCT(--(S1:S100=A1),--(J1:J100=A2))

Where A1 holds the Status to count and where A2 holds the processor's initials.

#### Jonmo1

##### MrExcel MVP
Here's your basic syntax for multiple conditional count

Code:
``=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2))``

NOTE that you cannot use entire column Refs like A:A, you must specify row #s Like A1:A1000..AND both ranges must be same size.

Code:
``=SUMPRODUCT((S1:S1000=3)*(J1:J1000="ABC"))``

P.S.
you can use pretty much the same formula for a multi condition SUM as well, just add the range to sum at the end
Code:
``=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2),RangeToSum)``

#### TinaP

##### Well-known Member
In between posting the questions and receiving your answers, I figured it out.

Thank you to both of you for such a quick response. For some reason, I was getting a #NUM! error before. Now, I can't recreate the error so that I can figure out what I did wrong.

#### Jonmo1

##### MrExcel MVP
The error was probably caused by either using entire Column Ref's, or the Range Refs not being equal size.

#### TinaP

##### Well-known Member
The error was probably caused by either using entire Column Ref's, or the Range Refs not being equal size.

I double checked both. I guess there's no use wracking my brain trying to recreate an error, I make enough without trying.
:wink:

Replies
0
Views
369
Replies
5
Views
186
Replies
4
Views
330
Replies
5
Views
184
Replies
3
Views
180

1,181,647
Messages
5,931,209
Members
436,784
Latest member
amuljono

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back