Trying to nest a SUM(FILTER inside a SUMIFS function

Question

New to the wonderful world of excel formulas and hoping someone has a solution to the below:

I am trying to use SUM/FILTER functions inside a SUMIF function to return results filtered by the column in the filter section as well as those matching a specific date in a date column

This is a formula that will be used across multiple reports so it's important to be able to filter by the column header

The =SUM(FILTER part of this formula works fine and returns the total value of the column

However when I try to add this to a SUMIFS Function and further filter the data so that only the total from results matching the date are returned

The current formula that works to return the total of the "Amount Spent" Column is =SUM(FILTER(A2:F393, A1:F1="Amount Spent"))

What I want to add this to is a SUMIFS so that the "Day" column is also searched to return the exact day

=SUMIFS(A2:F393, SUM(FILTER(A2:F393, A1:F1="Amount Spent"))

Similarly if I remove the FILTER/SUM =SUMIFS(A:F, A1:F1,"Amount Spent", A:A, A2) I get a #VALUE error message

Answer 1

Perhaps try something along the lines of using TOCOL() with FILTER()

---

=SUM(FILTER(TOCOL(A2:F13/(I1=A1:F1),2),A2:A13=DATE(2023,10,2)))

---

Or, Using SUMIFS()

---

=SUMIFS(INDEX(A2:F13,,MATCH(I1,A1:F1,0)),A2:A13,DATE(2023,10,2))

---

Answer 2

To use FILTER on both rows and columns, first FILTER by the columns, then FILTER that result by the rows:

=SUM(FILTER(FILTER($A$1:$F$12,($A$1:$F$1="Amount")),$A$1:$A$12=DATE(2023,10,2)))