Sumif Column Criteria Issue

[piper99]

hi,

I am trying to find below issue for formula based on below criteria. I need to set dynamic criteria for Product style, Year and Type (AUM or NNB). Help is appreciated.

C. Calculate the "% Organic AUM Growth" for each Product Style using a formula that reflects the corresponding values for each style based on the selected year.
D. Insert a dropdown menu in cell "B6" on the "Tables 1" worksheet. This dropdown should contain all the years available in the "Dataset" tab, allowing users to select a specific year. (example 2019)
E. Ensure the "% Organic AUM Growth" field updates dynamically to reflect the selected year from the dropdown menu, incorporating all available years in the dataset.

 

[NickFru]

Hello - if you create another data validation drop down say in cell B5 product style have year drop down as it is in cell B6, then try;

=SUMPRODUCT($I$4:$M$6,(($H$4:$H$6=A8)*($I$3:$M$3=$B$5)*($I$2:$M$2=$B$6)))

 

[piper99]

This might help but it will not help me profile for each product style and how about compare for organic growth based on 2 years

 

[NickFru]

Ok - you mentioned about comparisons between each product and in based on 2 years, hence, I have prepared the below in order to try and answer your points in how I understand them and would lay this out as follows;

Information as originally given;

	2021	2022	2021	2022	2023
Product Style	AUM	AUM	NNB	NNB	AUM
Product Style 2	100	300	200	100	300
Product Style 2	200	200	100	300	200
Product Style 3	300	100	300	200	100

Then I would produce your summary results like this to get year comparisons and in between relevant product style;

Year	2021	2022
Product Style	NNB	NNB
			Variance	% Change
Product Style 1	0	0	0​
Product Style 2	300	400	100​	33%
Product Style 3	300	200	-100​	-33%
Product Style 4	0	0	0​
Product Style 5	0	0	0​
Product Style 6	0	0	0​

Say years was in B1 and C1 for the comparisons and product style are in B2 & C2 respectively

So in the normal bolded cells C4 & D4 (in this example), use the formula;

=SUMIFS(INDEX($I$4:$M$6,,MATCH($B$1&$B$2,$I$2:$M$2&$I$3:$M$3,0)),$H$4:$H$6,$A8) then copy into D4 and copy down on both columns. This will give you the type of product style (ie product style 1, 2 etc, by the year and under product style reference code so you can evaluate comparatives). If you wanted to evaluate 2023 year to say 2021 year (or vice versa) for each product style, just simply use the data validation in cell C1 or B1

I have then produced a variance column which is just simply C4-B4 to get difference and then as a % under % change use the formula;

=IF(C4-D4=0,"",SUM(C4-B4)/B4*100%

If this isn't what you need, then I am struggling to help you out further but hopefully the above suggestions will help.