Add SUMPRODUCTS with Multiple Criteria

[JeffB]

The below question was in a previous post (Sumif with multiple criteria
question):

Given range named "type" and "qty", i am trying to write a formula that will
sum the "qty" of multiple "type"s together. I've tried
=sum(if((type="apples")+(type="oranges"),qty,0) and
=sumif(type,(or("apples"),("oranges")),qty)
neither work....can someone help me please?


T. Valko (Biff) posted a great solution to this question:

=SUMPRODUCT(--(ISNUMBER(MATCH(type,items,0))),qty)

I am using Excel 2003 and wondering if this formula can be further enhanced
to add item quantities if the quantities were spread out across multiple
locations in separate columns but only add desired locations (while ignoring
errors)?

Type Qty1 Qty2 Qty3 Qty4 ...
apples 2 1 0 4
oranges 10 8 4 5
bananas 4 6 18 9

I know that it could be simply added (SUMPRODUCT+SUMPRODUCT+….) or use:

=SUMPRODUCT(--(ISNUMBER(MATCH(type,items,0))),((qty1)+(qty2))

But I need something better and one that will ignore #N/A errors since I am
using INDEX and MATCH functions to find the appropriate columns which may or
may not be present on data sheets given to me on a monthly basis. Here is
the formula that I am using, but if the data is not present for one or more
of my “qty†columns, it returns an error value.

=SUMPRODUCT(--(ISNUMBER(MATCH(INDIRECT("'"&$A4&"'!$B$5:$B$1000"),IHLCC,0)))*((INDEX(INDIRECT("'"&$A4&"'!$A$5:$Z$1000"),0,MATCH(C$2,INDIRECT("'"&$A4&"'!$A$3:$Z$3"),0)))+(INDEX(INDIRECT("'"&$A4&"'!$A$5:$Z$1000"),0,MATCH(D$2,INDIRECT("'"&$A4&"'!$A$3:$Z$3"),0)))+(INDEX(INDIRECT("'"&$A4&"'!$A$5:$Z$1000"),0,MATCH(L$2,INDIRECT("'"&$A4&"'!$A$3:$Z$3"),0)))))

If I use =IF(ISERROR(…) for each “qtyâ€, the formula is too long for my
version of Excel 2003

Column A ("'"&$A4&"'!)= contains monthly data sheet names which makes it
easier to fill the table
IHLCC = “items’ named range
C2, D2, L2 = ‘qty†lookup values

Thank you for your help!
Jeff

 

[Don Guillett]

=-SUMPRODUCT((TRIM(ChecksC)={"electricity","water","sewer","garbage"})*(ChecksD))

 

[JeffB]

Hello Don,

Thank you for your reply, but you will have to forgive me. I am a self
taught Excel newbie and I try to make Excel bend to my will by reading posts
on this discussion group, reading the various help articles and manipulate
the formulas.

What I am trying to say is I don't understand your suggestion. I have been
testing it and can't get it to work. Will this work in Excel 2003? Can you
use it in a sentence by applying it to the apples and oranges formula or to
mine?

Thanks!
Jeff

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.

 

[JeffB]

Hello Don,

An email has been sent to you. I understand your formula now. Sorry for
the earlier lapse in brain power, I have been working on this for days now!

I like this version:

=SUMPRODUCT(--(ISNUMBER(MATCH(type,items,0))),((qty1)+(qty2))

because it allows me to use named ranges for the "items" part. What I would
like to do is modify the last part of the formula ...,((qty1)+(qty2)) to be
able to use a named range to lookup and add 'qty1' and 'qty2' columns and
ignore errorrs. I am using:

=SUMPRODCUT(--(ISNUMBER(MATCH(type,items,0))),((INDEX(Sheet2!A1:Z1000,0,MATCH("qty1",Sheet2!A3:Z3,0)))+(INDEX(Sheet2!A1:Z1000,0,MATCH("qty2",Sheet2!A3:Z3,0)))+...))))

Which works great unless one of my lookup columns is not present in the
data, then the dreaded #N/A appears.

Therefore I need a slick modification to the back half of this formula much
like the "(--(ISNUMBER(MATCH(type,items,0)" did for the front half.

All Excel Masters please reply!

 

[Don Guillett]

I did not get an email.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hello Don,

An email has been sent to you. I understand your formula now. Sorry for
the earlier lapse in brain power, I have been working on this for days
now!

I like this version:

=SUMPRODUCT(--(ISNUMBER(MATCH(type,items,0))),((qty1)+(qty2))

because it allows me to use named ranges for the "items" part. What I
would
like to do is modify the last part of the formula ...,((qty1)+(qty2)) to
be
able to use a named range to lookup and add 'qty1' and 'qty2' columns and
ignore errorrs. I am using:

=SUMPRODCUT(--(ISNUMBER(MATCH(type,items,0))),((INDEX(Sheet2!A1:Z1000,0,MATCH("qty1",Sheet2!A3:Z3,0)))+(INDEX(Sheet2!A1:Z1000,0,MATCH("qty2",Sheet2!A3:Z3,0)))+...))))

Which works great unless one of my lookup columns is not present in the
data, then the dreaded #N/A appears.

Therefore I need a slick modification to the back half of this formula
much
like the "(--(ISNUMBER(MATCH(type,items,0)" did for the front half.

All Excel Masters please reply!

 

[Bernd P]

Hello Jeff,

I suggest to consider a pivot table.

Regards,
Bernd

 

[JeffB]

Thanks Bernd, I will have to give that a try.

Don, I was wondering why there was no reply. I sent the email to
"(e-mail address removed)". I will try again.

Jeff

 

[Bernd P]

Hello Jeff,

If Don got it he will certainly deal with it, I think.

But you can send it to my google address as well - it might take some
time for me to answer, though.

Regards,
Bernd

 

[JeffB]

Hello Bernd,

I just sent an email to you. If you don't get it, let me know. I work for
a hospital with an hyper-active firewall that may prevent messages being sent
to certain addresses.

Thank you for your offer as well!
Jeff

 

[Bernd P]

Hello Bernd,

I just sent an email to you.  If you don't get it, let me know.  I work for
a hospital with an hyper-active firewall that may prevent messages being sent
to certain addresses.

Thank you for your offer as well!
Jeff
--
From there to here, from here to there, funny things are everywhere.








- Zitierten Text anzeigen -

Got it...let me get home...

Regards,
Bernd

 

[Bernd P]

Hello,

I send it back now. Its a textbook example for not using SUMPRODUCT:
http://sulprobil.com/html/sumproduct.html

This code collects the input data for some pivot tables:
Option Explicit

Sub collect_raw_data()
Dim vWS As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim m As Long 'month
Dim obj As Object
Dim sSite As String
Dim sRejSuperClass As String

Set obj = CreateObject("Scripting.Dictionary")
Sheets("Param").Select
i = 2
Do While Not IsEmpty(Cells(i, 1))
obj(Cells(i, 1).Text) = Cells(i, 2).Text
i = i + 1
Loop

With Sheets("Temp")
..Range("A2:G65536").ClearContents 'Clean output range
k = 1 'indicates last row in output area - currently nothing = 1
(title row)
For Each vWS In Worksheets
vWS.Select
'Cells() now refers to sheet vWS, .Cells() to sheet Temp
If Right(vWS.Name, 4) = "Data" Then
If Not IsEmpty(Cells(3, 3)) Then
m = Application.WorksheetFunction.Match(UCase(Left
(vWS.Name, 3)), _
Array("JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL",
"AUG", _
"SEP", "OCT", "NOV", "DEC"), 0) 'm becomes number of
month
i = 5 'row index
Do While Not IsEmpty(Cells(i, 2))
If UCase(Right(Cells(i, 2), 5)) <> "TOTAL" Then
If Cells(i, 1) <> "" Then sSite = Cells(i, 1)
j = 3 'column index
Do While Not IsEmpty(Cells(3, j))
If UCase(Right(Cells(3, j), 5)) <> "TOTAL"
Then
If Cells(2, j) <> "" Then sRejSuperClass =
Cells(2, j)
If Cells(i, j) > 0 Then
k = k + 1
.Cells(k, 1) = DateSerial(Right(Cells
(1, 2).Text, 4), m, 1)
.Cells(k, 2) = sSite
.Cells(k, 3) = Cells(i, 2)
.Cells(k, 4) = sRejSuperClass
.Cells(k, 5) = obj(Cells(3, j).Text)
'reject class
.Cells(k, 6) = Cells(3, j) 'reject
code
.Cells(k, 7) = Cells(i, j)
End If
End If
j = j + 1
Loop
End If
i = i + 1
Loop
End If
End If
Next vWS
..Select 'go back to sheet Temp
End With

Set obj = Nothing 'release storage

End Sub

Regards,
Bernd

 

[JeffB]

THANK YOU!!! You have also showed me there is so much more to learn.

Jeff

 

[Don Guillett]

Where col A has the code to look for and row 1 has the sheet name to look in
row 3 of each sheet has the 3ltr code
and we want the last item in the column using the sheet col B as the
reference
Copy across and down (correct word wrap)

=IF(ISNA(MATCH($A3,INDIRECT(C$1&"!$3:$3"),0)),"",OFFSET(INDIRECT(C$1&"!A$3"),MATCH("zzz",INDIRECT(C$1&"!$B:$B"))-2,MATCH($A3,INDIRECT(C$1&"!$3:$3"),0)-1))

 

[JeffB]

Thank you Don and Bernd,

The amount of knowledge presented in this discussion group is amazing!
Thank you for sharing.

Jeff