YTD Calculation using dynamic columns and conditions

[mrxell]

Hi folks, I am badly stuck with an excel calculation for YTD numbers, where the start date and end date are variable. The columns are in columns another input tab. I want to be able to fetch the YTD data by customer/produc/distributor, etc. where these are the conditions dependend on another input file. Already spent so many hours figuring out the solution but no use.. FYI, i tried Sumif Index Match combinations, Sumif offset combination, sumif xlookup combination but all failed as they cannot sum the range of columns rather just work on adding two columns for the start and end date. Attaching sample data file for reference.. Someone pleaseeeee help!

Input FIle: Mothly data is from col E to AL starting Sep 2020 to Jun 2023
Output required: YTD based on YTD Start CY and CY in output tab

 

[awoohaw]

I'm not sure this is a complete solution. But it may get you started down the path. I would suggest using the xl2bb add in (link below) to help the forum help you. The forum needs to recreate your scenario and that takes time and can have unknown errors and errors in assumptions. For instance, your cross tabs have fields that are not explained and where they come from.

If you cannot use the xl2bb, then posting a table of data would be helpful.

But, below is a solution, I think for one of your #REF errors. Although I'm not certain it is what you want.

Best wishes.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2
3	Country	Distributor	Product	Source	2020-09-01	2020-10-01	2020-11-01	2023-04-01	2023-05-01	2023-06-01
4	Country1	Distributor1	Product1	New Customer	495	495	495	611	611	611
5	Country2	Distributor2	Product2	Price Increase	0	0	0	0	0	0
6	Country3	Distributor3	Product3	New Customer	0	0	0	0	0	0
7	Country4	Distributor4	Product4	Price Increase	0	0	0	0	0	0
8	Country5	Distributor5	Product5	New Customer	495	495	495	611	611	572
9	Country6	Distributor6	Product6	Price Increase	495	495	495	611	611	572
10	Country7	Distributor7	Product7	Price Increase	0	0	0	0	0	0
11
12
13
14
15		New Customers by Distributor			2020-09-01	2020-10-01	2020-11-01	2023-04-01	2023-05-01	2023-06-01
16		Distributor1			495	495	495	611	611	611
17		Distributor3			0	0	0	0	0	0
18		Distributor5			495	495	495	611	611	572
19
20
21
22
23
24
25
26		New Customers by Product
27		Product1			495	495	495	611	611	611
28		Product3			0	0	0	0	0	0
29		Product5			495	495	495	611	611	572
30
31
Sheet1

Cell Formulas
Range	Formula
B16:B18	B16	=TAKE(FILTER($B$4:$D$10,(D4:D10="New Customer")),,1)
E16:J18	E16	=SUMIFS(E$4:E$10,$B$4:$B$10,$B16)
B27:B29	B27	=CHOOSECOLS(FILTER($B$4:$D$10,(D4:D10="New Customer")),2)
E27:J29	E27	=SUMIFS(E$4:E$10,$C$4:$C$10,$B27)
Dynamic array formulas.

 

[mrxell]

Thank you so much for your response. I am actually unable to install the xl2bb add-in as my computer says "this file type is not supported in protected view", tried looking for some solutions but could not. However, I am looking for help with YTD column (Col i & J) in the output tab, where the COl I should provide the sum of all columns ranging between C1 and C2 plus matching the distributor type in A20, Source in cell C4. Sharing the copy of the data table for your perusal. Kindly help!

Input table

Country	Distributor	Product	Source	Sep-20​	Oct-20​	Nov-20​	Apr-23​	May-23​	Jun-23​
Country 1	Distributor 1	Product 1	New Customer	495​	495​	495​	611​	611​	611​
Country 2	Distributor 2	Product 2	Price Increase	0​	0​	0​	0​	0​	0​
Country 3	Distributor 3	Product 3	New Customer	0​	0​	0​	0​	0​	-39​
Country 4	Distributor 4	Product 4	Price Increase	0​	0​	0​	0​	0​	0​
Country 5	Distributor 5	Product 5	New Customer	495​	495​	495​	611​	611​	572​
Country 6	Distributor 6	Product 6	Price Increase	495​	495​	495​	611​	611​	572​
Country 7	Distributor 7	Product 7	Price Increase	0​	0​	0​	0​	0​	0​

Output table

	CY	Jun-23	PY	Jun-22								44742​
May-23	YTD Start - CY	Apr-23	YTD Start - PY	May-22								5/1/2022​
Apr-24	FY End - CY	Apr-24	FY End - PY	Apr-23								45046​
	Source	New Customer
New Customers
	Current Month (Jun-23)							Year-to-Date (Jun-23)
	Act	Bud	PY	v Bgt	v PY	YoY%		Act	Bud	PY	v Bgt	v PY	YoY%
New customers by product
Product 1	611		622
Product 2	-		-
Product 3	- 39		9
Product 4	-		-
Product 5	572		691
Product 6	-		-
Product 7	-		-
Total	1,144		1,322					-
New customers by Distributor
Distributor 1	611		622
Distributor 2	-		-
Distributor 3	- 39		9
Distributor 4	-		-
Distributor 5	572		691
Distributor 6	-		-
Distributor 7	-		-
Total	1,144		1,322
New customers by country
Country 1	611		622
Country 2	-		-
Country 3	- 39		9
Country 4	-		-
Country 5	572		691
Country 6	-		-
Country 7	-		-
Total	1,144		1,322