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 horizonta...
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)