Formula Errors

How to Calculate Positive Subtotals by Journal Entry in Excel

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs an Excel formula to calculate the sum of only positive lines for specific journal entries while respecting hidden or filtered detail rows.

Product: Excel
Device & OS: not provided
Scenario: Calculating journal entry totals where some detail rows are hidden or filtered out, and only positive values should be included in the final subtotal per group.
Observed behavior: Standard SUMIF formulas do not ignore hidden rows, requiring an advanced combination of SUMPRODUCT, SUBTOTAL, and OFFSET functions to correctly calculate positive values based on visibility and group identifiers.

Before you start

Ensure your journal entry identifiers and numerical values are consistently formatted in their respective columns, and clearly identify which rows contain your target data ranges before applying array formulas.

Solution 1Recommended

Use Advanced SUMPRODUCT for Standard Journal Entries

This formula combination evaluates multiple criteria, checking for positive values, matching journal entry IDs, and ensuring the row is currently visible.

By nesting SUBTOTAL and OFFSET inside a SUMPRODUCT function, you force Excel to check the visibility of each individual row in your array. This overcomes the limitation of standard conditional sum formulas that calculate hidden rows.

Select the target cell

Click on the cell where you want the positive subtotal to appear for your journal entry.

Enter the formula

Input the following formula: =SUMPRODUCT(($B$2:B3>0)*($A$2:A3=--LEFT(A4,6))*SUBTOTAL(3,OFFSET($B$2,ROW($B$2:B3)-ROW($B$2),))*$B$2:B3)

Adjust data ranges

Modify the ranges ($B$2:B3 for values, $A$2:A3 for IDs) in the formula to match the actual layout of your accounting sheet.

Apply and drag

Press Enter to calculate the result, then click and drag the fill handle down to copy the formula to other journal entry groups.

Important Note on Array Formulas: Depending on your Excel version, you may need to press Ctrl + Shift + Enter to evaluate this as an array formula if it returns a #VALUE! error.

Solution 2

Apply Formula Variation for Multi-Line Groups

Use this alternative variation if the initial formula successfully calculates two-line groups but fails or returns errors for entries with more than two lines.

Advanced Spreadsheets with WPS Office

Easily Calculate Complex Subtotals in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array combinations like SUMPRODUCT, SUBTOTAL, and OFFSET. You can flawlessly calculate conditional subtotals on visible rows while enjoying seamless compatibility with your existing Microsoft Excel workbooks.

1. Open your workbook: Launch WPS Spreadsheet and open your journal entry .xlsx file.
2. Select the result cell: Click on the cell where you want to output the positive subtotal.
3. Input the formula: Paste your SUMPRODUCT and SUBTOTAL combination directly into the formula bar.
4. Calculate and update: Press Enter. The result will instantly calculate and dynamically update whenever you hide or unhide detail rows.

100% compatible with Microsoft Excel formulas, functions, and .xlsx formats.Intuitive interface for managing filtered, grouped, and hidden rows seamlessly.Free, lightweight, and fast-loading alternative to traditional office suites.

microsoft office alternative - wps office

Frequently Asked Questions

Why does SUMIF include hidden rows in my journal entries?

The standard SUMIF and SUMIFS functions are programmed to evaluate all cells within a specified range, regardless of whether they are hidden or filtered. To selectively ignore hidden rows, you must use the SUBTOTAL or AGGREGATE function.

What does the SUBTOTAL(3, ...) part of the formula do?

The number 3 in the SUBTOTAL function corresponds to the COUNTA function. When nested inside an OFFSET array within SUMPRODUCT, it generates a binary array of 1s and 0s that tells the formula whether each specific row in the range is currently visible (1) or hidden (0).

Can I use this formula to calculate negative subtotals instead?

Yes. You can adapt the formula by changing the condition that checks for positive values. Simply change the '>0' operator (e.g., in the segment '($B$2:B3>0)') to '<0' to sum only the negative lines while still ignoring hidden rows.

Why do I need the OFFSET function in this combination?

SUBTOTAL normally evaluates an entire range at once, which returns a single value. By wrapping OFFSET inside it, you feed the range to SUBTOTAL one single row at a time. This forces Excel to return an array of visibility statuses for each row, which SUMPRODUCT can then multiply against your actual data values.

Excel & Data Problems