count with multiple criteria

TinaP

Well-known Member
Joined
Jan 26, 2005
Messages
528
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
Joined
Aug 12, 2005
Messages
976
=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
Joined
Oct 12, 2006
Messages
44,061
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.

so for your example, maybe
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
Joined
Jan 26, 2005
Messages
528
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
Joined
Oct 12, 2006
Messages
44,061
The error was probably caused by either using entire Column Ref's, or the Range Refs not being equal size.
 

TinaP

Well-known Member
Joined
Jan 26, 2005
Messages
528
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:
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top