Use Excel SUMIFS to Calculate Uninvoiced Amounts by Month
Question details
Calculate the total amount to invoice for a selected month while excluding invoice lines that have already been invoiced.
- Product
- Microsoft Excel spreadsheet formula
- Device & OS
- not provided
- Scenario
- A worksheet contains multiple invoice lines for companies or projects, and the user needs a monthly total for lines marked as not invoiced.
- Observed behavior
- The desired result is a total that matches the selected company or project, selected month, and an invoice status of N.
Confirm that the Amount to Invoice values are numeric and that the Month and Invoiced Y/N columns use consistent values. Use the exact column headers and criteria-cell references that appear in your worksheet.
Use SUMIFS with Excel Table column criteria
Use this method when your invoice data is stored in an Excel Table and you need a flexible total based on company or project, month, and invoice status.
SUMIFS adds values only when every specified criterion is met. In this example, A2 contains the company or project, B1 contains the selected month, and N identifies an uninvoiced line.
Select any cell in the source data, then choose Insert > Table. Confirm that My table has headers is selected, and assign or note the table name, such as Data, on the Table Design tab.
Select the result cell and enter =SUMIFS(Data[Amount to Invoice],Data[Company],A2,Data[Month],B1,Data[Invoiced Y/N],"N"). Replace Data and each column name with the table and header names used in your workbook.
Enter the required company or project in A2 and the month value in B1. The formula recalculates the total for matching uninvoiced invoice lines.
Use SUMIFS with regular worksheet ranges
Use this option if the invoice data has not been converted to an Excel Table.
Calculate Monthly Uninvoiced Totals with SUMIFS in WPS Spreadsheets
WPS Spreadsheets supports the SUMIFS function for calculating uninvoiced amounts from multiple criteria. Create a structured invoice list, enter your company or project and month criteria, and use one formula to return the matching total.
- 1. Open the invoice worksheet: Open the workbook in WPS Spreadsheets and verify that the amount, company or project, month, and invoice-status columns have headers.
- 2. Create criteria cells: Enter the company or project to report in a cell such as A2 and enter the required month in a cell such as B1.
- 3. Select the total cell: Click the cell where the uninvoiced monthly total should appear.
- 4. Enter the SUMIFS formula: Type =SUMIFS(Data[Amount to Invoice],Data[Company],A2,Data[Month],B1,Data[Invoiced Y/N],"N") and replace the table and column names with your own headers.
Frequently Asked Questions
Why does my SUMIFS formula return zero for uninvoiced amounts?
Check that the invoice-status cells contain exactly N, without extra spaces, and verify that the month criterion in B1 has the same value or date format as the Month column. Also confirm that Amount to Invoice cells are stored as numbers.
Can SUMIFS calculate totals for a specific project instead of a company?
Yes. Replace Data[Company] with the project column, such as Data[Project], while keeping the selected project cell as the corresponding criterion.
How do I total uninvoiced amounts for all companies in one month?
Remove the company or project criteria pair from the formula. For example, use =SUMIFS(Data[Amount to Invoice],Data[Month],B1,Data[Invoiced Y/N],"N").
Does SUMIFS require an Excel Table?
No. You can use standard worksheet ranges, but each sum range and criteria range must cover matching rows. Tables make formulas easier to read because they use column headers.
