SumIf multiple criteria help!

You can make a new column, say column AA. In cell AA1 put the formula:
=IF(AND(A1="TKM",M5=50),Z1,"")

Then you can just sum column AA. If you want, you can hide column AA if it might be confusing to anyone. There are other ways to do this, but this should work fine.

HTH

Shawn --

SUMIF cannot handle multiple conditions (excepting a between condition). The following will do what you want:

=SUMPRODUCT((Sheet1!A2:A100="TKM")*(Sheet1!M2:M100=50),(Sheet1!Z2:Z100))

If the conditions are in cells of their own, you can use these cells intead of "TKM" and 50.

Aladin

==========

Hi, I tried using this formula:
=SUMPRODUCT(('Actual Data'!$E:$E="TKM")*('Actual Data'!$L:$L=50),('Actual Data'!I:I))
and I get #NUM! as a result.
the 'Actual Data' is the name of the sheet that the data's coming from.

What am I doing wrong?

Sorry to butt in, but this is a quick fix.
Sumproduct doesn't like columns such as E:E. change it to
=SUMPRODUCT(('Actual Data'!E1:E100="TKM")*('Actual Data'!L1:L100=50),('Actual Data'!I1:I100))

or to some rows that works now and will give you room to grow.

better make that..

ERR

Re: better make that..

Okay, now I'm getting #VALUE instead.
Any Ideas?

=SUMPRODUCT(('Actual Data'!E1:E50000="TKM")*('Actual Data'!L1:L50000=50)*('Actual Data'!I1:I50000))

: Hi, I tried using this formula

Re: better make that..

> Okay, now I'm getting #VALUE instead.
> Any Ideas?

Shawn --

First things first. Ian's observation about whole columns as args to SUMPRODUCT is impossible, unless... I'll come back to that later.

That #VALUE! error seems to suggest that you have formula produced blanks in either the Code column or the Dollars column or both.

Right?

Aladin

========== =SUMPRODUCT(('Actual Data'!E1:E50000="TKM")*('Actual Data'!L1:L50000=50)*('Actual Data'!I1:I50000))

Re: better make that..

The range that I use in the caculation contains a ton of blanks because the number of records keeps growing.

I could send you a copy of the spreadsheet if you want to look at it.

Re: better make that..

I could send you a copy of the spreadsheet if you want to look at it.

OK.

Aladin

==========

Re: better make that..

I sent you a copy of the file. The file might ask you to refresh the data when you open it up because It occasionaly adds data through Oracle. Just tell it you don't want to refresh.

Thanks,

The range that I use in the caculation contains a ton of blanks because the number of records keeps growing. : I could send you a copy of the spreadsheet if you want to look at it. OK.

Re: better make that..

Sorry if I caused problems with this. Will one of you please post the corrected formula when you have it?
thanks,
Ian

The #VALUE! Error & Whole Columns As Args

Ian --

> Sorry if I caused problems with this. Will one of you please post the corrected formula when you have it?

You didn't & you know it.

The biggest trouble that one can get with the array or SUMPRODUCT formulas is that dreaded #VALUE! error.

In Shawn's case, it's fortunately a benign one that doesn't require expanding the suggested formula (now with the real ranges):

=SUMPRODUCT(('Actual Data'!$E$2:$E$561="TKM")*('Actual Data'!$L$2:$L$561="50"),'Actual Data'!$I$2:$I$561)

That 50 is not a number, put more precisely, L is not of numeric type, whence the double quotes.

By the way, I added a UDF (due to Harlan Grove) to the workbook that enables you to rewrite the above formula as:

=SUMPRODUCT((USED('Actual Data'!$E:$E)="TKM")*(USED('Actual Data'!$L:$L)="50"),(USED('Actual Data'!$I:$I)))

The code of the UDF is:

Function Used(r As Range) As Range
'
' Harlan Grove
' Sun 25 Nov 01
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))
End Function

Aladin

Re: The #VALUE! Error & Whole Columns As Args

> Sorry if I caused problems with this. Will one of you please post the corrected formula when you have it? You didn't & you know it. The biggest trouble that one can get with the array or SUMPRODUCT formulas is that dreaded #VALUE! error. In Shawn's case, it's fortunately a benign one that doesn't require expanding the suggested formula (now with the real ranges): =SUMPRODUCT(('Actual Data'!$E$2:$E$561="TKM")*('Actual Data'!$L$2:$L$561="50"),'Actual Data'!$I$2:$I$561) That 50 is not a number, put more precisely, L is not of numeric type, whence the double quotes. By the way, I added a UDF (due to Harlan Grove) to the workbook that enables you to rewrite the above formula as: =SUMPRODUCT((USED('Actual Data'!$E:$E)="TKM")*(USED('Actual Data'!$L:$L)="50"),(USED('Actual Data'!$I:$I))) The code of the UDF is: Function Used(r As Range) As Range Harlan Grove Sun 25 Nov 01

I may have to expand my horizons into VBA a bit, amazing the things you can do. Unless you respond to the contrary, I'll assume you made a similar typo (paste-o) as I did earlier. I'll mess up the term and argument terminology, but I'm assuming you meant to multiply the three terms (arguments?)together, and not have one agrument multiplying two terms, and second argument after the comma.

A belated welcome home and best wishes for a successful '02.

Ian

Re: The #VALUE! Error & Whole Columns As Args

Ian --

Thanks. The comma here is OK. It obeys the syntax of the function:

SUMPRODUCT(array1,array2,...)

Here array1 has 1's and 0's by multiplication, array2 are the dollar amounts. SUMPRODUCT then multiplies them before summing. Essentially, when we make SUMPRODUCT count that we have a single array.

Aladin

This will work:

Enter the Following formula in the cell you want the answer to appear: =Sum(If(A1:A?="TKM",if(M1:M?=50,Z1:Z?))) then press Ctrl+Shift+Enter (CSE).

Replace the question mark in the formula with the row number for your last entry. Make it 6,000 or so if you plan to fill the sheet and don't want to keep changing the formula. Just remember to enter the formula by pressing CSE.

Hope this helps!
Aron