Forum Discussion
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 horizontal row. Two sheets within one spreadsheet. It should be easy. The data tab is in columns.
This is where I'm trying to insert the formula ... where you see #NAME?. The second table is the data that I'm trying to retrieve in total by month ...
What am I doing wrong?
Thanks in advance.
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.
- Your data is on a sheet called Data and the columns are structured as follows:
So what appears to be the problem is that the function name is SUMIFS and not SUMSIF. As for how to use SUMIFS you can see Kidd_Ip response but with dates you need to be careful be "Oct-2024" (assuming it IS a date and not just text) could be ANY day in Oct-2024. So Kidd_Ip's solution assumes that is 10/01/2024 to match how you have your headers in the table. IF those dates could be OTHER days in that month then try this:
=SUMIFS(Data!B:B, Data!A:A, ">="&CriteriaDate, Data!A:A, "<"&EDATE(CriteriaDate,1), Data!C:C, CriteriaMarket, Data!D:D, CriteriaEventID)NOTE: I also changed the columns so they match the order you show in your screenshot (the amounts are in col B)
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 !
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.
Thank you SO MUCH! I was trying to get there, but it wasn't working. I did have to exchange a couple of columns to get the dollars, but it works. Have a great weekend!
- Your data is on a sheet called Data and the columns are structured as follows:
