Complete Solution: Sales Tracker Analysis

Summary

Three tasks delivered: a SUMPRODUCT-based weighted average by region, a MAXIFS formula to find the top-revenue month, and a simple comparison formula to flag calculation mismatches. All formulas assume standard headers in row 1.

1. Weighted Average Price Per Region

Assuming data starts at A2:G[n], paste this in any cell (e.g., H2):

=SUMPRODUCT((B2:B1000="East")*(D2:D1000)*(E2:E1000))/SUMIF(B2:B1000,"East",D2:D1000)

Replace "East" with your target region name. This multiplies quantity by unit price per row, sums those products, then divides by total quantity for that region — giving you the true weighted average, not a simple AVERAGE of Unit Price.

If you want a summary table instead (all regions at once), create a helper column I2:I[n]:

=D2*E2   /* Revenue per row */

Then use

=SUMPRODUCT((B$2:B$1000=B2)*(I$2:I$1000))/SUMIF(B$2:B$1000,B2,D$2:D$1000)

Drag down — each row shows its region's weighted average for easy comparison.

2. Month with Highest Revenue

If Date is column A and Total is column G, use this to get the top month's name:

=INDEX(TEXT(A$2:A$1000,"YYYY-MM"),MATCH(MAX(SUMIF(TEXT(A$2:A$1000,"YYYY-MM"),TEXT(A$2:A$1000,"YYYY-MM"),G$2:G$1000)),SUMIF(TEXT(A$2:A$1000,"YYYY-MM"),TEXT(A$2:A$1000,"YYYY-MM"),G$2:G$1000),0))

This returns something like "2024-03". If you just want the dollar amount:

=MAX(SUMIF(TEXT(A$2:A$1000,"YYYY-MM"),TEXT(A$2:A$1000,"YYYY-MM"),G$2:G$1000))

3. Flag Calculation Mismatches

In column H2, paste this:

=IF(ABS(D2*E2-G2)>0.01,"CHECK","OK")

The 0.01 tolerance handles floating-point rounding. Any row marked "CHECK" means the manual Total column disagrees with Qty×Price — either a typo was made or the Total uses a discount code. Filter H to show only "CHECK" rows and audit them.

Verification Steps

1. Check one weighted average against a pivot table grouping by Region
2. Cross-check the top revenue month against a monthly bar chart
3. Spot-check flagged rows against original invoices

Note: SUMPRODUCT/SUMIF with entire-column references (B2:B1000) works on Microsoft 365 and Excel 2021+. For Google Sheets, replace ARRAYFORMULA wrappers and use QUERY() instead.