Count or Sum based on multiple criteria in multiple columns in Excel

This table and explanation provide a breakdown of invoices and customers based on specific conditions like date range, status, and city. Here’s the detailed explanation:

Input Conditions

• Start Date: January 3, 2022
• End Date: February 14, 2022
• Status: Paid
• City: Athens

These conditions filter the data to only include invoices and customers:

1. Within the date range from 1/3/2022 to 2/14/2022.
2. Having the status “Paid.”
3. (For customers) belonging to Athens city.

Metrics Calculated

1. Count of Invoices (Start Date, End Date, Status): 3
   Formula: =SUM((Date>=C3)*(Date<=D3)*(Status=E3))
   • Filters rows where the Date is between 1/3/2022 and 2/14/2022 and Status = Paid.
   • From the table:
     • Reese Withpoon (1/24/2022, Paid)
     • Clint Eastwood (2/14/2022, Paid)
     • Kanye East (1/18/2022, Paid — excluded from “City: Athens” but counted here).
2. Sum of Invoices (Start Date, End Date, Status): 934
   Formula: =SUM((Date>=C3)*(Date<=D3)*(Status=E3)*Amount)
   • Sums the Amount for invoices where the conditions above are true.
   • Breakdown:
     • Reese Withpoon: 279
     • Clint Eastwood: 231
     • Kanye East: 424
     • Total: 279 + 231 + 424 = 934
3. Count of Customers (Start Date, End Date, Status, City): 2
   Formula: =SUM((Date>=C3)*(Date<=D3)*(Status=E3)*(City=G3))
   • Filters rows further to only Athens city.
   • From the table:
     • Reese Withpoon (1/24/2022, Athens)
     • Clint Eastwood (2/14/2022, Athens)
     • Count = 2.
4. Sum of Invoices (Start Date, End Date, Status, City): 510
   Formula: =SUM((Date>=C3)*(Date<=D3)*(Status=E3)*(City=G3)*Amount)
   • Sums the Amount for “Paid” invoices within the date range for Athens city.
   • Breakdown:
     • Reese Withpoon: 279
     • Clint Eastwood: 231
     • Total: 279 + 231 = 510

Conclusion

The table and formulas calculate counts and sums for invoices and customers that meet specific criteria (date range, status, and city). It effectively filters and processes the raw data to produce meaningful insights:

• 3 invoices are “Paid” within the date range.
• 2 customers are from Athens with “Paid” invoices, totaling 510 in amounts.