Microsoft Excel - SumIf VBA Help

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

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:


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?

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:

I would also consider using Access  rather than Excel.  It is better suited to your needs.

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.

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

Hope this will help you

Thank you all for your responses!  Sorry for the delay in responding back. 

PiChartY,
I was able to adapt your code easily to the code I've already written and it works beautifully.  I did have a question about the For Each UnqProd section of the code.  How does VBA know what UnqProd is if you never declare that? 

Thanks again for helping me out!

JD

Hi JD, You have same quest with me when I first use Vba. Actually you can use any word instead of unqProd. Some one use just "X", "Y" or "a","b", etc. Then how do vba know? Because when we use For each .....in range(.....) This is already declair that we want to get the things in that range. With the "Next UqdProd" below, tell vba to loop the second, third...till last thing in that range, finally exit loop. The sequence of loop, always left to right then top to bottom of the range. If the range is "a1:a3" vba will go a1,a2 finally a3. If the range is "a1:c3", vba will go a1,b1,c1,then a2,b2 ... End at c3. Hope this help Any question you have, please feel free to ask again. Pichart Y.