# Microsoft Excel - SumIf VBA Help - Asked By J D on 16-Mar-12 04:03 PM

Hello All,

First post on the boards, so please bear with me.  I'm stuck with a section of code involving a sumif function.  I'm a beginner VBA user but have an above average Excel 2007 & 2010 base.   I have 3 named ranges in my spreadsheet, NDCRange, UniqueNDCs, and IASTQTY.  NDCRange contains a long list of product codes, UniqueNDCs contains the unique product codes from that list, and IASTQTY contains a quantity of products corresponding to each row in the NDCRange.  My sumif needs to take each cell in the UniqueNDCs range and calculate the total quantity.  For example:

NDCRange       IASTQTY           UniqueNDCs
111111              5              111111
111111              5              111112
111113              10             111113
111112              5
111112              15

My result needs to be:
111111       10
111112       20
111113       10

So far I have been able to correctly get the first sumif calculated correctly, however, it gives the same result for all the cells in the UniqueNDCs range. My problem is figuring out how to get the sumif criteria  to loop through all the cells in the UniqueNDCs range.  I've tried modifying the criterian = range ("UniqueNDCs").value

Below is my code:

Sub IASTSums()

Dim IAST As Worksheet
Dim EXP As Worksheet
Dim NDCRange As Range
Dim UniqueNDCs As Range
Dim IASTQTY As Range
Dim IASTResult As Long
Dim Cell As Range
Dim Criterian

Criterian = Worksheets("IAST").Range("AH2").Value
IASTResult = WorksheetFunction.SumIf(Range("NDCRange"), Criterian, Range("IASTQTY"))
Worksheets("IAST").Select
For Each Cell In Range("UniqueNDCs")
ActiveCell.Value = IASTResult
ActiveCell.Offset(1, 0).Select
Next Cell

End Sub

Any help would be greatly appreciated!  This is part of a larger macro and needs to be automated because the end user doesn't even know how to copy down the formula.

Thanks so much,

JD
wally eye replied to J D on 16-Mar-12 07:02 PM
It sounds like this isn't an option, but a couple of formulas:

=IF(OFFSET(UniqueNDCs,ROW(1:1)-1,0)>0,OFFSET(UniqueNDCs,ROW(1:1)-1,0),"")

and

=SUMIF(NDCRange,A2,IASTQTY)

Since that isn't an option for you, a bit of VBA.  Go to the VBA IDE (alt-F11), insert a new module, and paste in this code:

`Public` `Sub` `CalcTotalQty() `
` `
`  ``Dim` `rngStart          ``As` `Excel.Range `
`  ``Dim` `lngRows         ``As` `Long`
` `
`  ``Set` `rngStart = ThisWorkbook.Names(``"UniqueNDCs"``).RefersToRange.Cells(1, 1) `
`  ``lngRows = ThisWorkbook.Names(``"UniqueNDCs"``).RefersToRange.Rows.Count `
` `
`  ``rngStart.Offset(0, 1).Resize(lngRows, 1).Formula = ``"=SUMIF(NDCRange,"` `& rngStart.Address(``False``, ``True``) & ``",IASTQTY)"`

`  ``Set` `rngStart = ``Nothing`
` `
`End` `Sub`

All it does is put the formulas in for you.  It assumes the named range UniqueNDCs scope is Workbook, if it is tied to a worksheet then it will have to be tweaked a bit.  If you want hard values instead of the formulas, just add a line after the autofill:

rngStart.Offset(0, 1).Resize(lngRows, 1) = rngStart.Offset(0, 1).Resize(lngRows, 1).Value

You can associate this with a button on your sheet, I think you have that part well in hand.  I assume you have a dynamic range for UniqueNDCs, something like:

=offset(Sheet1!A2,0,0,counta(Sheet1!A:A)-1,1)

So when you add/remove items the named range still reflects the correct range?
Pat Hartman replied to J D on 16-Mar-12 10:19 PM
I would export the NDCRange to Access, run a query that sums the data and then reimport the results.  The query to do the sum is very simple:
`Select` `NDCRange, ``Sum``(IASTQTY) ``as` `SumOfIASTQTY`
`From` `MyTable`
`Group` `By` `NDCRange;`

I would also consider using Access  rather than Excel.  It is better suited to your needs.
Pichart Y. replied to J D on 17-Mar-12 11:56 AM
Hi JD,

I give you here some idea for you to extend to your spreadsheet...

I separate your question into 2 steps, I assign the code to the buttons, on the last button call both steps to run...
1. Create unique product list from your product list, I create the unique list in column E...using this code
• Sub createUniqe()
Range("E2:F5000").ClearContents
Range("E1") = "UniqueNDCs"
LastProdRow = Range("A" & Rows.Count).End(xlUp).Row
For Each prod In Range("A2:A" & LastProdRow)
If WorksheetFunction.CountIf(Range("E:E"), prod) = 0 Then
Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = prod.Value
End If
Next prod
Range("E2:E" & LastProdRow).Sort Key1:=Range("E2"), Order1:=xlAscending
MsgBox "Unique Product List was created successfully"
End Sub
2. In steps 2, we input the summation of each product in column F, using this code
• Sub sumQtyByProd()
Range("F1") = "Sum Qty By Product"
lastUnqRow = Range("E" & Rows.Count).End(xlUp).Row
For Each UnqProd In Range("E2:E" & lastUnqRow)
UnqProd.Offset(0, 1).Value = WorksheetFunction.SumIf(Range("A:A"), UnqProd, Range("B:B"))
Next UnqProd
MsgBox "Summary By Product is finished"
End Sub
3. Finally I group both code into one sub, using..
• Sub runAllCode()
Call createUniqe
Call sumQtyByProd
End Sub
Try this...example attachment for you -->Vba_Sumif.zip

Of course, this is just intend to give you some idea, then you can adjust to your own spreadsheet, if you face any difficulty or want to ask more, please feel free to raise your question again.

Hope this help.

Pichart Y.
Reena Jain replied to J D on 19-Mar-12 02:02 AM
hi,

As I know in Excel, the SumIf function adds all numbers in a range of cells, based on a given criteria.

The syntax for the SumIf function is:

SumIf( range, criteria, sum_range )

range is the range of cells that you want to apply the criteria against.

criteria is used to determine which cells to add.

sum_range are the cells to sum.

Applies To:

• Excel 2007, Excel 2003, Excel XP, Excel 2000
So you can use the same function in excel 2003 as well