I have data in date wise which excel formula I use that separate date by monthly and sum total value.
Month Report.xlsx
I have data in date wise which excel formula I use that separate date by monthly and sum total value.
Month Report.xlsx
Last edited by meraz; 01-23-2023 at 04:33 AM.
Put the date of the first day of the month (i.e. 1/04/2022) into the merged cell K3/K4, then you can use this formula in cell L5:
=SUMIFS(INDEX(Table1[[Place 1]:[Place 5]],,MATCH($K5,Table1[[#Headers],[Place 1]:[Place 5]],0)),Table1[Mode Type],L$4,Table1[Date],">="&$K$3,Table1[Date],"<="&EOMONTH($K$3,0))
Copy this across into M5, then copy those two formulae down to complete the first table.
You can use the same formula for the lower tables, but you must first put the date in K13/K14 (i.e. 1/05/2022), and then copy the formula from L5 into cell L15 and change the references shown in red above so that the formula looks at the dates in $K$13, then copy across and down as above. Repeat this for the other tables.
Hope this helps.
Pete
Another
=SUMPRODUCT((Table1[[Place 1]:[Place 5]])*(Table1[Mode Type]=L$4)*(Table1[Date]>=$K$2)*(Table1[Date]<=EOMONTH($K$2,0))*(Table1[[#Headers],[Place 1]:[Place 5]]=$K5))[/FORMULA]Formula:Please Login or Register to view this content.
As per Pete's post: put month in K2 and change formula above for other months
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks