This example demonstrates:
| ® | Setting up a Products and Services item to provide the text that will trigger a subtotal calculation |
| ® | Using the item on a QuickBooks Online form, to control where subtotals occur |
| ® | Entering a macro worksheet formula to subtotal a column |
| ® | Connecting the Products and Services item to the subtotal formula |
| ® | How to have column subtotals and totals |
 | This technique works on any QuickBooks Online form which has numeric column(s) such as the Qty or Amount column. |
Required reading... This discussion assumes you've read the Column totals (QBOL) topic, so please read that topic first if you have not done so already. |
Column Subtotals with FullSpeed
As this is written, QuickBooks Online does not have the ability to provide subtotals on data entry forms (invoices, purchase orders, etc.). A FullSpeed macro can give you subtotals, and they work almost identically to totals, which were described in the Column total (QBOL) topic)
This example shows how to subtotal the quantity (Qty) column on a QuickBooks Online form.
Getting Ready
Set up a subtotal item in QuickBooks Online
Instead of using existing Products and Services items to trigger FullSpeed calculations, usually you will set up a new item for that purpose. This assures the item won't record income unless you want it to (if the Price/Rate is set to 0) and it lets the item provide any descriptive information you may want.
Here's a new item, SubtotQty, added for subtotaling a column.
Notes:
| ® | The item Name and Description can be anything you want. |
| ® | We've left the Description blank, for less clutter on QuickBooks Online forms. But you can enter a description if you want. |
| ® | The Account you select doesn't matter much because the Price/Rate is set to 0, so no income or expense should be recorded when this item is used. |
Use the item on a form
Select the SubtotQty item on the line immediately below each group of lines you want to subtotal.
Enter a formula for subtotaling the Qty column, and its trigger text
| 1. | Enter the formula on a text-triggered formula row. |
The Qty column is the third column on the form (see the screenshot above), so put the formula in at least the third column of the macro worksheet. You need to leave room to access the form's first column—the Item column—because it contains the trigger item, SubtotQty.
| 2. | Enter the trigger text on the same row. |
That way, when FullSpeed encounters the trigger text in QuickBooks Online it will calculate the formula.
| Though optional, entering column names on the Column or field names row makes it easier to remember how the macro worksheet's columns relate to the invoice. |
Notice that this formula actually sums the Sandbox rows of the Qty column. That is a shorthand way of telling FullSpeed to sum the entire Qty column when the macro runs.
| The easy way to enter the formula would be as: =SUM(E0:E0). FullSpeed converts row 0 references to Sandbox row references, so you don't need to know what they are. |
Choose macro options
Choose appropriate options for running the macro:
| ® | Mark the Qty column as the Begin field. (Any column would do; it's a matter of choice.) |
| ® | Mark the Product/Service column as the Match column. That tells FullSpeed where to look for the trigger text. |
| ® | Use macro options toolbar settings as shown below—especially the target application (QuickBooks Online) and the repeat type (Run until "X" blank fields). |
| ® | Assign a hotkey. Shift+Ctrl+3 is assigned in this example. |
| Remember to save the FullSpeed file, to avoid losing your work! |
Running the Macro
| 1. | Click on the first cell in the Qty column in QuickBooks Online. |
Why the first cell? Because FullSpeed macros process a form downward from where the macro is invoked (run). To total the entire Qty column we must begin at the top of the column.
Why the Qty column? Because that's what we selected as the Begin field in the macro worksheet.
| 2. | Press the hotkey assigned to the macro, Shift+Ctrl+3 in this example. |
FullSpeed will traverse the form, gathering data. As it reaches each SubtotQty row it will write a subtotal in the Qty field.
Can I have Subtotals and Totals?
Yes! You can nest any number of subtotal and total levels on your forms. Just use a different trigger item for each level.
Here's the same example as the above, but with a TotalQty item (from the Column totals (QBOL) topic's example) added to the form (note the green arrow):
And here is the macro worksheet with the TotalQty name and formula added on a separate text-triggered formula row:
Notes:
| ® | The TotalQty formula sums the same range of cells as the SubtotQty formula. Why? Because there's no way in FullSpeed for the total formula to refer to add up the individual subtotal rows. The only difference in how these two formulas work is caused by the fact that the SubtotQty item appears more than once on the invoice. Each time it appears, FullSpeed resets the subtotal amount to 0 before processing more rows. |
Finally, here's the result of a macro run with both subtotals and a total:
Can I Subtotal the Amount Column?
Yes. The technique is only slightly different and is discussed in the next topic, Subtotaling the Amount column (QBOL).