PDA

View Full Version : Solved: Sum a column based on multiple criteria in different columns



hardoo
11-14-2012, 02:45 PM
Hi everybody I’m seeking your quick help for this VBA code. ActuallyI’d like to create a VBA code that would make me able to sum a column if there are some criteria matched.

So, here is the example:
I've Sheet1 wich have ( Product , Customer, Status, Quantity ) Columns.
I've Sheet2 Wich have ( Product , Customer, Status, Total Quantity ) Columns.

What i need to Sum Column Quantity from Sheet1 and put the result in Total Quantity in Sheet2 if >> Columns ( Product , Customer, Status ) are matched with the other Columns in Sheet2.



The result should be appeared in sheet2 in Total Quantity Column

Sheet1:

Product---------------Customer---------------Status---------------Quantity

A---------------Ctmer1---------------1---------------7

A---------------Ctmer1---------------2---------------3

B---------------XCtmer0001---------------4---------------20

C---------------XCtmer0002---------------4---------------20

A---------------Ctmer2---------------3---------------5

C---------------XCtmer0002---------------3---------------20

A---------------Ctmer3---------------2---------------10



Sheet2:

Product---------------Customer---------------Status---------------Total Quantity

A---------------Ctmer1, Ctmer2, Ctmer3---------------1, 2---------------20

Bob Phillips
11-14-2012, 04:51 PM
Use a formula

=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!B:B,B2,Sheet1!C:C,C2)

hardoo
11-15-2012, 04:29 AM
Thank you xld for your quick response:yes .

Actually I know this formula but I need to have something similarto this formula in VBA due to lots of criteria rows to be matched from Sheet2so, it would be like using loop and get the results.

I hope to help me on that please J

hardoo
11-15-2012, 04:30 AM
Thank you xld for your quick response J
Actually I know this formula but I need to have something similarto this formula in VBA due to lots of criteria rows to be matched from Sheet2so, it would be like using loop and get the results.
I hope to help me on that please J

hardoo
11-15-2012, 12:10 PM
Any good idea to make that happen in VBA instead of SUMIFSformula

p45cal
11-16-2012, 07:19 AM
1. What version of Excel are you using?
2. Does the sumif formula suggested by xld work? If so it's quite easy, but I suspect it won't due to the multiple entries in a single cell ("Ctmer1, Ctmer2, Ctmer3"). However, there may be a solution…

Bob Phillips
11-16-2012, 11:15 AM
Why is the status for product A only 1,2 not 1,2,3 and why is the total quantity 20 not 25?

p45cal
11-16-2012, 11:42 AM
xld, I suspect the OP is looking for the total of the Quantity column for rows where:
the product is A
the customer is one of Ctmer1, Ctmer2, Ctmer3
the Status is either 1 or 2

that is if the OP hasn't moved on by now…

I've written a macro to generate the formulae (an inefficient sumproduct formula, different on each row) and I've written one to create, manipulate and extract totals from a pivot table, but I feel they're cumbersome and will look at trying to SQL the data table. And of course it wouldn't be very hard to loop through the data row-by-row, cell by cell, but there's no challenge to that and I'd only provide such a solution if (a) the OP still has an interest and (b) I haven't come up with something nicer.

hardoo
11-17-2012, 09:15 AM
Hi p45cal, xld

Thank you very much for your interesting.

Firstly, p45cal I’m very happy that you got my point.

Yes, I’m looking for the total of the Quantity column forrows where:


A) The product is A
B) The customer is one of Ctmer1, Ctmer2, Ctmer3 (“for the multiple entries in a single cell”)
C) The Status is either 1 or 2 (“for the multipleentries in a single cell”)

Secondly, With regards to the conditions B) and C) beinformed that they have multiple entries in a single cell as I mentioned above andthat’s why I need to do that via VBA code looping through this huge number ofrows because the worksheet has a lot of data to be looping through.

So, your help/solution will be much appreciated J

p45cal
11-17-2012, 09:57 AM
1. What version of Excel are you using??

hardoo
11-17-2012, 10:36 AM
Excel 2003

p45cal
11-17-2012, 03:36 PM
Excel 2003
Damn! My favourite solution depended on the new way of referring to tables and their columns introduced in Excel version after 2003. I'll write a user defined function or a macro to to it.
I succeeded in writing one using ADO & SQL, but again it's centered around the more recent versions of Excel and I can't be bothered to have a fight with connection strings and the like.

p45cal
11-17-2012, 05:45 PM
OK, copy down the formula in D2 which is:
=myQuantity(A2,B2,C2,Sheet1!$A$1:$D$8)
9102
There's a link to the screenshot here too (https://www.box.com/s/zjftkijn4bv725diy1gs).
The range Sheet1!$A$1:$D$8 therein is the Source data on sheet1; it includes the headers in the screenshot, but doesn't have to - in fact it's better not to include the headers - the user defined function assumes the source data columns are in the order Product, Customer Status, Quantity. If you use the Function Arguments Dialogue box to populat the arguments, it's easy to see what goes where. Essentially:
=myQuantity(CellContainingProductList,CellContainingCustomerList, CellContainingStatusList,DataSourceRange)
I've not tested this in Excel 2003 - fingers crossed.

backed up by the code of the user defined function:Function myQuantity(Products, Customers, Statuses, DataSource)
myQuantity = "Error"
TheProducts = Split(Products, ",")
TheCustomers = Split(Customers, ",")
TheStatuses = Split(Statuses, ",")
TQ = 0
For Each rw In DataSource.Rows
ProductFound = False: CustomerFound = False: StatusFound = False
If UBound(TheProducts) < LBound(TheProducts) Then
ProductFound = True
Else
For Each Product In TheProducts
If Trim(Product) = Trim(rw.Cells(1)) Then
ProductFound = True
Exit For
End If
Next Product
End If
If ProductFound Then
If UBound(TheCustomers) < LBound(TheCustomers) Then
CustomerFound = True
Else
For Each Customer In TheCustomers
If Trim(Customer) = Trim(rw.Cells(2)) Then
CustomerFound = True
Exit For
End If
Next Customer
End If
If CustomerFound Then
If UBound(TheStatuses) < LBound(TheStatuses) Then
StatusFound = True
Else
For Each Status In TheStatuses
If Trim(Status) = Trim(rw.Cells(3)) Then
StatusFound = True
Exit For
End If
Next Status
End If
If StatusFound Then
If IsNumeric(rw.Cells(4).Value) Then
TQ = TQ + rw.Cells(4).Value
End If
End If
End If
End If
Next rw
myQuantity = TQ
End Function

hardoo
11-18-2012, 07:54 AM
p45cal,
Thank you very much for your greate solution. Really you helped me to save alot of efforts and manual work.