Need SUMPRODUCT formula to calculate Cumulative Totals
Hi
Please help to find formula to calculate YTD Cumulative totals from below mentioned table, if select particular month in another table
APAC: Jan Feb Mar Qtr1
Pacific 1 2 3 6
SEA 1 2 3 6
Japan 1 2 3 6
Korea 1 2 3 6
India 1 2 3 6
China 1 2 3 6
6 12 18 36
Hi and welcome to the forum.
Pasting data into your post doesn't really help us help you as the layout is skewed and we have no idea of your expected results.
Could you attach a workbook showing your data layout and manually create some results so we can understand the requirement?
Click on "Go Advanced" below your reply, then "Manage Attachments" to open the upload window.
BSB
Thanks for Prompt Response,
please find enclosed data sample, i have calculated MTD numbers for Mar month, similarly i want to calculate QTD and YTD for Mar Month
Assuming that: month, APAC is same order between both year (the two table is same size, particular month in same column, countries are in same order)
All the month must be real date, formatted as MMM, i.e MAR is 03/01/2019 (First day of the month)
Sheet data: C3=Date(S3,1,1)
D3=EOMONTH(MAX($B3:C3),0)+1
Copy to Dec
Copy row 3 to row 13
Sheet Result: In B3, type 1st day of the month, i.e 04/01/2019 (year is fixed to 2019)
C5:
Drag to D5
=SUMPRODUCT((Data!$B$4:$B$9=$B$4)*(Data!$C$3:$S$3=$B$3)*IF(C$3=2019,Data!$C$4:$S$9,Data!$C$14:$S$19))
F5:
Drag to G5=SUMPRODUCT((Data!$B$4:$B$9=$B$4)*(Data!$C$3:$R$3<=$B$3)*(Data!$C$3:$R$3>=DATE(2019,CHOOSE(MONTH($B$3),1,1,1,4,4,4,7,7,7,10,10,10),1))*IF(F$3=2019,Data!$C$4:$R$9,Data!$C$14:$R$19))
I5:
Drag to J5=SUMPRODUCT((Data!$B$4:$B$9=$B$4)*(Data!$C$3:$R$3<=$B$3)*IF(I$3=2019,Data!$C$4:$R$9,Data!$C$14:$R$19))
Last edited by bebo021999; 04-18-2019 at 04:19 AM.
Quang PT
Thanks a lot, its working absolutely fine
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks