A SUMIFS total includes the wrong invoice references: check wildcards

A spreadsheet can calculate correctly under a matching rule that the finance team never intended to use. If an invoice, purchase-order or customer reference contains an asterisk or question mark, a conditional total may treat that character as a wildcard. The formula can then include several different references while displaying the requested reference beside one plausible amount.

Microsoft documents wildcard behavior in SUMIFS criteria: a question mark can stand for one character, while an asterisk can stand for a sequence. A tilde allows specified wildcard characters to be treated literally. This matters when a reference is an identifier rather than a search pattern. Establish which meaning the workbook needs before correcting a formula or explaining a difference to accounting.

Keep the source transactions unchanged while investigating. Record the requested identifier, the formula and the unexpected total. Compare the underlying rows instead of adding an adjustment that makes the summary agree. A spreadsheet matching error should be repaired in the calculation; it does not establish that any original invoice amount was wrong.

Consider four invented purchase-order references and their outstanding amounts: PO*17 has $240, POA17 has $90, PO-17 has $60 and PO17 has $110. Assume the references occupy cells A2 through A5 and the numeric amounts occupy B2 through B5. The task is to total only the literal reference PO*17.

The formula =SUMIFS(B2:B5,A2:A5,"PO*17") returns $500 for this set. The asterisk is interpreted as a pattern, so all four references qualify. The desired literal result is $240. The $260 difference is the sum of three unrelated references, not an unexplained accounting adjustment. This compact example makes the cause visible without circulating live supplier information.

Use a similarly small sample from the affected identifier structure, with sensitive details replaced appropriately. Include at least one reference that should match and one that should not. A test containing only the intended reference cannot expose the unwanted match. Retain the expected rows and total so the proposed repair can be checked independently.

A deliberate pattern can be useful. A manager may want every reference beginning with a project prefix, and a wildcard can express that request. A reconciliation for one exact purchase order has a different purpose. Make that distinction explicit in the workbook input: is the user selecting an individual key or entering a pattern for a group?

Pointing the criterion at another cell does not, by itself, settle the issue. SUMIFS accepts cell references as criteria, and wildcard content remains relevant to the comparison. If the input cell contains PO*17, review how the formula interprets it. Moving the same text out of the formula and into a dropdown is not an adequate repair without testing the returned population.

Inspect the source identifier convention. Some systems allow punctuation in an external reference even when their internal record identifier uses another format. Where a stable internal key is available and appropriate for the report, assess using it for the relationship while retaining the readable reference for users. Do not silently replace meaningful characters in source references to accommodate the spreadsheet.

For the invented control set, =SUMIFS(B2:B5,A2:A5,"PO~*17") returns the intended $240. The tilde tells the criterion to treat the asterisk as a character in the reference. This is a change to the comparison expression, not a request to rename the source purchase order PO~*17. Preserve the original identifier in the transaction record.

A reusable workbook also needs to handle literal question marks and tildes. Microsoft documents the tilde escape convention for those characters. Have the formula owner design a controlled helper where necessary and test identifiers containing each character, combinations of them and ordinary text. If the helper performs substitutions, the order matters: it must not re-escape characters that it just inserted for another substitution.

Keep the helper separate from the displayed identifier and label its purpose. A reviewer should be able to see the original value, the criterion used and the resulting matched rows. If the business intentionally supports pattern searches elsewhere, leave those rules distinguishable. Applying literal treatment indiscriminately could break a legitimate grouped report while fixing an exact-key reconciliation.

Identify other formulas using the same input field or copied comparison logic. Focus on the dependency that caused the error rather than reviewing every workbook in the business. A shared template may feed supplier statements, project balances or an exception list. Check the outputs that could have inherited the same unwanted matches.

Compare row membership as well as the final amount. Positive and negative transactions can offset, leaving an apparently correct total assembled from incorrect records. A credit against another purchase order still belongs to that other order even if it happens to cancel an extra invoice included by the wildcard. Keep the row-level comparison with the repaired calculation.

Confirm that the amount and criteria ranges cover the same intended rows. Microsoft requires matching dimensions for SUMIFS ranges. The wildcard repair does not resolve an omitted final row, a wrong entity filter or a different reporting cutoff. Check those relevant boundaries before describing the revised summary as reconciled, while keeping any separate issue identified under its own cause.

Save the small control set and its expected result with the workbook instructions. Explain why punctuation is escaped and which reports intentionally use patterns. When the next preparer adds a reference or copies the formula into a new schedule, that example provides a quick way to detect whether the intended matching behavior survived.

If an incorrect summary was used in a decision or sent to another team, identify the affected version and provide the corrected result through the normal review process. State that the change concerns selection of records, unless investigation also found a source error. The completed repair should explain both why the old total included extra references and how the new calculation selects only the intended population.