Note: tried in Excel and Google Sheets, but I have a preference for Sheets.
Basically I want to get the sum of a group of data using INDEX and MATCH (because the parameters are going to be drop-down dependent):
The desired result is:
So this will require a few things:
- Converting the cell D13(April) to a Month
- Converting the "weekof" column to a Month
- Using INDEX and MATCH and MATCH again, I'm assuming because it's multiple cell references.
Here's my solution currently below:
=SUM(INDEX(D5:I9, MATCH(MONTH(D13&1),ARRAYFORMULA(MONTH(C5:C9)),0), MATCH(E12,D4:I4,0)))
This returns the NEAREST value:
270
Instead of:
804
Why this value?
270 500 34 = 804
CodePudding user response：
If you are not strict to use INDEX and MATCH, you may use the following solution:
Add extra column name it "Month", this column will extract the month name from the date column using TEXT function as the following:
=IF(C3<>"",TEXT(C3,"mmmm"),"")
The if statements ensures that only filled dates will have a month value, since you have to fill this column with the above formula for a certain amount of cells.
Now you can simply use the SUMIF function in cell E13 or where ever you want:
=SUMIF(B:B,D13,D:D)
If you don't want the Month column to appear within your data table you may put it at the end of your table and hide it.
CodePudding user response：
You can use pivot table and group dates by year and month.