Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-25-2017, 08:21 PM
Lee-Ann Lee-Ann is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Sep 2017
Posts: 2
Lee-Ann is on a distinguished road
Default 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
Attached Files
File Type: xlsm SalesBook.xlsm (17.8 KB, 5 views)
Reply With Quote
  #2  
Old 09-26-2017, 12:17 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Advanced Beginner
 
Join Date: May 2017
Posts: 60
ArviLaanemets is on a distinguished road
Default

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)
Reply With Quote
  #3  
Old 09-26-2017, 12:21 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Advanced Beginner
 
Join Date: May 2017
Posts: 60
ArviLaanemets is on a distinguished road
Default

I forgot attatchment, and it looks like I can't add it to previous post anymore.
Attached Files
File Type: zip SalesBookEdited.zip (20.7 KB, 2 views)
Reply With Quote
  #4  
Old 09-26-2017, 11:17 PM
Lee-Ann Lee-Ann is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Sep 2017
Posts: 2
Lee-Ann is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 09-27-2017, 02:26 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 136
Debaser is on a distinguished road
Default

Do you really need the subtotals within the data? Using a pivot table alongside it would be extremely simple.
Attached Files
File Type: xlsm SalesBook v2.xlsm (20.6 KB, 2 views)
Reply With Quote
  #6  
Old 09-27-2017, 10:11 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Advanced Beginner
 
Join Date: May 2017
Posts: 60
ArviLaanemets is on a distinguished road
Default

Quote:
Originally Posted by Lee-Ann View Post
We need to have our categories count and sum under each invoice just by clicking a button or a short key.
And then you search for certain invoice over huge number of rows! (~200 invoicers per day, ~300 workdays in year , ~5 rows per invoice + rows for counts and sums, + 2 empty rows per invoice. And no way to use autofilter.

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.
Reply With Quote
  #7  
Old 09-28-2017, 07:38 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 388
NoSparks is on a distinguished road
Default

Hello Lee-Ann,

Do you really need formulas in those cells or would the values produced by the formulas be sufficient ?
Attached Files
File Type: xlsm SalesBook_v3.xlsm (32.9 KB, 0 views)

Last edited by NoSparks; 09-29-2017 at 05:44 AM. Reason: add file
Reply With Quote
Reply

Tags
excel vba, sumif in vba
Please reply to this thread with any new information or opinions.

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


All times are GMT -7. The time now is 04:38 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft