#1
|
|||
|
|||
Sumif within VBA
Hello All,
I don't have any knowledge about VBA, so if someone can help I'll be thankful. I have a single sheet of workbook for our daily sales data entry with 6 active columns (A-F). I group our sales per Invoice number. At the end of each invoice I have to add 3 rows of countif (in column E) and 3 rows of sumif (in column F) manually. I have tried to record a macro with countif and sumif formulas but as you can see no luck. It's messing up the sheet, specially because my data entry rows per each invoice are dynamic. I would like to make things automatically, because our invoices can reach over 250 every day. As you can see in my workbook (attached with VBA), I would like to have my calculations 1 row below after each invoice group. The VBA that I have recorded is the following: Code:
Sub sumif() ' ' Macro2 Macro ' ' Selection.End(xlDown).Select ActiveCell.Offset(2, 3).Range("A1").Select ActiveCell.FormulaR1C1 = "CATEGORY (A110)" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "CATEGORY (B220,C330)" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "CATEGORY (D440,E550)" ActiveCell.Offset(-2, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-7]C[-2]:R[-2]C[-2],""110"")& "" ITEMS""" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=COUNTIF(R[-8]C[-2]:R[-3]C[-2],""A220"")+COUNTIF(R[-8]C[-2]:R[-3]C[-2],""C330"")&"" ITEMS""" ActiveCell.Offset(1, 0).Range("A1").Select Selection.FormulaR1C1 = _ "=COUNTIF(R[-9]C[-2]:R[-4]C[-2],""D440"")+COUNTIF(R[-9]C[-2]:R[-4]C[-2],""E550"")&"" ITEMS""" ActiveCell.Offset(-2, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUMIF(R[-7]C[-3]:R[-2]C[-3],""A110"",R[-7]C:R[-2]C)" ActiveCell.Offset(1, 0).Range("A1").Select Selection.FormulaR1C1 = _ "=SUMIF(R[-8]C[-3]:R[-3]C[-3],""B220"",R[-8]C:R[-3]C)+SUMIF(R[-8]C[-3]:R[-3]C[-3],""C330"",R[-8]C:R[-3]C)" ActiveCell.Offset(1, 0).Range("A1").Select Selection.FormulaR1C1 = _ "=SUMIF(R[-9]C[-3]:R[-4]C[-3],""D440"",R[-9]C:R[-4]C)+SUMIF(R[-9]C[-3]:R[-4]C[-3],""E550"",R[-9]C:R[-4]C)" ActiveCell.Offset(1, 0).Range("A1").Select Selection.End(xlToLeft).Select End Sub |
#2
|
|||
|
|||
For data entry use a normalized table - then you don't need VBA at all.
I redesigned your workbook as I would do it. Probably all you need is setting filters on SalesData sheet and reading count and sum at top of sheet. When this isn't enough, you can design any number of report sheets, where you can determine one or several filtering conditions like year, Invoice, Salesman, etc. (use validation lists to select them), and rest of info is read from SalesData sheet in exactly such format as you need it. Edit. With such design, you haven't enter data into SalesData table in some predetermined order. And you can use also ODBC query to get p.e. some summary data into another table (and use parameters read from worksheet cells for WHERE clause of query). Edit. Edit. Using ODBC queries to read data from SalesData table, you can also design separate individualized report files p.e. for bookkeeping, or for executive board members, etc. (The source workbook must be on network resource then, of-course) |
#3
|
|||
|
|||
I forgot attatchment, and it looks like I can't add it to previous post anymore.
|
#4
|
|||
|
|||
Thank you ArviLaanemets for your reply. your table is perfect and neat but unfortunately it's not an answer to our daily task. We need to have our categories count and sum under each invoice just by clicking a button or a short key.
|
#5
|
||||
|
||||
Do you really need the subtotals within the data? Using a pivot table alongside it would be extremely simple.
|
#6
|
|||
|
|||
Quote:
As I said, you can design a report in any form you want. Probably the best one will be getting a list of all invoices for certain period (date or month or year - with your number of invoices a date will be reasonable choice) with ODBC query from InvoiceData table. The query will be executed automatically whenever period in cell linked to query parameter is changed. The query will return p.e. invoice number and date. You add columns adjacent to query table for every count and sum, and calculate according values using COUNTIFS() and SUMIFS() functions from InvoiceData table. And you get all needed info in compact (table) form, can use autofilters on report and subtotals on top of report sheet like I used on InvoiceData sheet (I don't have Excel at home, so I can only hope I haven't completly forgotten what name I used for sheet). Sums and counts under (but better above, as you never know before, how many rows will be in some invoice) an invoice you can have also - when you design a report for single invoice. Again the easiest way is to use query with invoice number as parameter. The query will return full invoice info from InvoiceData table, and you use COUNTIFS() and SUMIFS() to calculate totals from query table. |
#7
|
|||
|
|||
Hello Lee-Ann,
Do you really need formulas in those cells or would the values produced by the formulas be sufficient ? Last edited by NoSparks; 09-29-2017 at 05:44 AM. Reason: add file |
Tags |
excel vba, sumif in vba |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SUMIF with Or | Lluewhyn | Excel | 6 | 02-17-2017 10:48 AM |
Another SUMIF | OTPM | Excel | 6 | 06-06-2016 03:00 AM |
VBA sumif | Nisio07 | Excel Programming | 0 | 02-26-2016 07:53 AM |
Need help sumif with variable for VBA | jingo | Excel Programming | 4 | 01-23-2014 11:02 AM |
Average if and sumif | jennamae | Excel | 4 | 01-17-2014 05:10 AM |