Forum Discussion

debdyer's avatar
debdyer
Copper Contributor
Apr 04, 2025
Solved

Excel formula SUMSIF or DATA/MATCH?

I'm trying to write a formula using SUMSIF to pull a total amount from a column based on date, market and eventid -- so three criteria.  Two criteria are in vertical columns and one is in a horizonta...
excel
Formulas and Functions
training
  • Kidd_Ip's avatar
    Kidd_Ip
    Apr 04, 2025

    How about using SUMIFS:

     

    • Your data is on a sheet called Data and the columns are structured as follows:
      • Date in column A
      • Market in column B
      • EventID in column C
      • Amount in column D
    • Your criteria (dates, markets, and event IDs) are in the current table where you're inserting the formula.
    =SUMIFS(Data!D:D, Data!A:A, CriteriaDate, Data!B:B, CriteriaMarket, Data!C:C, CriteriaEventID)

    You'll replace CriteriaDate, CriteriaMarket, and CriteriaEventID with the relevant cell references in your current table.

Islamay's avatar
Islamay
Copper Contributor
Apr 04, 2025

It looks like you're trying to sum values based on three criteria:

Market (Column in Data Table)
Event ID (Column in Data Table)
Month (Row in Summary Table)

Since you're using SUMIFS, you need to handle the month-based criteria correctly, as your months are in a horizontal layout.

Formula Using SUMIFS

Try using this formula in the cell where you currently see #android?:

excel

CopyEdit

=SUMIFS(Data!$B:$B, Data!$C:$C, $A2, Data!$D:$D, $B2, Data!$A:$A, TEXT(C$1, "MMM-YYYY"))

Breaking Down the Formula:
Data!$B:$B → Column with "Detail Amt" (sum range)
Data!$C:$C → Column with "Market" (criteria 1)
$A2 → The corresponding Market value in the summary table
Data!$D:$D → Column with "Event ID" (criteria 2)
$B2 → The corresponding Event ID in the summary table
Data!$A:$A → Column with "Activity Period" (criteria 3)
TEXT(C$1, "MMM-YYYY") → Converts your date header (e.g., 1/1/2022) into the format "MMM-YYYY" to match the Activity Period (e.g., "Jan-2022").
Possible Issues:
#NAME? Error → Means Excel doesn't recognize a function. Make sure SUMIFS is correctly spelled and your range references are valid.
Date Format Mismatch → Ensure that "Activity Period" in your data is stored as real Excel dates, not text. If necessary, try TEXT(Data!$A:$A, "MMM-YYYY").
Column References → Double-check that Data!A:A, Data!B:B, etc., correctly reference your data columns.

Let me know if you need further adjustments !

Resources