Multiple Condition Sumif?

Use the following array formula construct...

{=SUM((range1=range2)*range3)}

RoB,

(1) Following non-array formula

=SUMPRODUCT((ISNUMBER(MATCH(Comm2001.xls!Lender_Sort,A18:A26,0))*(Comm2001.xls!Fees_Sort))) =SUMPRODUCT((Comm2001.xls!Lender_Sort=A18:A26,Comm2001.xls!Fees_Sort)

and

(2) its array brother:

{=SUM((ISNUMBER(MATCH(Comm2001.xls!Lender_Sort,A18:A26,0))*(Comm2001.xls!Fees_Sort))) =SUMPRODUCT((Comm2001.xls!Lender_Sort=A18:A26,Comm2001.xls!Fees_Sort)}

Aladin

Aladin,
Could you check this again? I tried using this code, but it didn't work. It returned a #VALUE. The cell is formatted to display currecny (which is what this is counting)
Thanks,
Rob

Mine works! : )

Mine (will work) too ;)

RoB,

My initial instinct about what is asked for was precisely like Mark's,

that is:

{=SUM((Comm2001.xls!Lender_Sort=A18:A26)*Comm2001.xls!Fees_Sort)}

which must be array-entered. Or, equivalently, which is not array-formula,

=SUMPRODUCT((Comm2001.xls!Lender_Sort=A18:A26)*Comm2001.xls!Fees_Sort)

However, I adopted a different interpretation of your query, for which I proposed:

=SUMPRODUCT((ISNUMBER(MATCH(Comm2001.xls!Lender_Sort,A18:A26,0))*(Comm2001.xls!Fees_Sort)))

and its array-entered brother:

{=SUM((ISNUMBER(MATCH(Comm2001.xls!Lender_Sort,A18:A26,0))*(Comm2001.xls!Fees_Sort)))}

Note. I noticed that both were pretty messed up in my initial post.

You have 2 interpretations, each with 2 alternative formulas (SUMPRODUCT Vs Array Formula).

Cheers,

Aladin

Ok, another question

Its still not working for me. What else could I be doing wrong?

Thanks for the input

Re: Ok, another question

RoB,

Care to post 5 rows from each range that you have?

Aladin

Re: Ok, another question

I made a simple simulation page, and it doesnt work either. See if you can find whats wrong if you dont mind.

Thanks again

Re: Ok, another question

RoB,

This is your data set in A1:B18 (including the labels "Lender" and "Fee":

{"Lender","Fee";"bank one",1;"bank one",1;"bank one",1;"bank one",1;"bank one",1;"bank one",1;"chevy",1;"chevy",1;"key bank",1;"key bank",1;"key bank",1;"bank one",1;"key bank",1;"key bank",1;"bank one",1;"chevy",1;"chevy",1}

In G1:G2, you have:

{"chevy";"key bank"} [ note that I corrected a typo here ]

I selected all of the cells of A2:A18 and named it via the Name Box: LENDERS. I named B2:B12 FEES.

In H1 enter: =SUMPRODUCT((LENDERS=G1)*FEES) [ copy down to H2 ]

You used the label Lender instead of a name that you should have given to the range A2:A12 as I've done above or just use A2:A12.

Just for comparison:
In I1 array-enter: =SUM((LENDERS=G1)*FEES) [ remember hitting control+shift+enter to enter this formula, then copy down ]

Both should give you the same result.

Aladin

ok, little more help please, sorry heh

I got that part to work, a single array or sumif isnt a problem. But when I try the formula:
=SUMPRODUCT((LENDERS=G1:G2)*FEES)
it doesnt give an answer.

I was trying to keep this as an example to prevent myself from explaining what I'm trying to accomplish, but I think Im going to have to.

I'm trying to do a double condition sumif. Let me modify the file and look at it again if you dont mind :)

Thanks for all the help aladin.

more help please, part deux....correct link :)

I got that part to work, a single array or sumif isnt a problem. But when I try the formula:
=SUMPRODUCT((LENDERS=G1:G2)*FEES)
it doesnt give an answer.

I was trying to keep this as an example to prevent myself from explaining what I'm trying to accomplish, but I think Im going to have to.

I'm trying to do a double condition sumif. Let me modify the file and look at it again if you dont mind :)

Thanks for all the help aladin.

RoB: Hyperlink is not working!

See below Aladin

Pleased...

immensely to see/to discover that my MATCH interpretation was/is right in the ballpark:

=SUMPRODUCT((ISNUMBER(MATCH(LENDERS,G10:G11,0))*(FEES)*(LOANOFFICER=G3)))

Aladin

YES!!! ONE more thing Aladin

THANKS so much Aladin! it works perfectly. One more thing though. What would be the command if I wanted to COUNT, and not SUM with the exact same conditions? I don't think there is a COUNTPRODUCT is there? Thanks again

What about...

=SUMPRODUCT((ISNUMBER(MATCH(LENDERS,G10:G11,0))*(ISNUMBER(FEES))*(LOANOFFICER=G3)))

=====================

No, there isn't. I reckon it's not needed. ;)

Great!!! thanks SOooo much