count with multiple criteria

[TinaP]

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.

 

[ExcelChampion]

=SUMPRODUCT(--(S1:S100=A1),--(J1:J100=A2))

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

 

[Jonmo1]

Here's your basic syntax for multiple conditional count

=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

=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

=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2),RangeToSum)

 

[TinaP]

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]

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

 

[TinaP]

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: