Sumifs in Multiple Sum Range set up Columns using a dropdown list of multiple categories

[fritzkie_alexander]

Good Day!

Im trying to finish my report but stuck on this one last formula to map my data to the source.

At first i tried sumifs to fix the issue however i have multiple sumrange ie per column header calculations. That i need to match the column header range to my drop down list to perform the function. Dead end for me. I also tried to apply sum(index(match),match) but the result is not providing me a sum of the entire range per month eve per day.

i got 2 drop downlist and my problem is with the sub lob drop down list also with the months , weeks and days. thank you in advance. a UDF VBA is also highly appreciated but i hope i could also get an answer with an excel formula. Cheers!

 

[Aladin Akyurek]

Probably...

Let A1:Z40 house the data with headers.

Control+shift+enter, not just enter:

=SUM(IF(A2:A40="jon",IF(A1:Z1="X",A2:Z40)))

 

[fritzkie_alexander]

Hi Aladin,

Really appreciate you looking into my problem, you are among the very few person i have in mind that can fix this issue. The formula that you've provided only address the fist parameter which is the sub LOB. We still need to add the month as part of the condition/parameter to sum the selected range columns. Please note that this is extremely large data file currently reach 600000 + rows a YTD Report.

Let me explain that further below.


In Sheet 1

List of drop down
LOB = 1st drop down list
Sub LOB = 2nd drop down list (the first parameter/condition)


(Row 1) Metrics Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Note: Months 2nd condition
(Row 2) Total Aux Time sum for each months


In Sheet 2 (Source)

Note: Sub LOBs starts from Column 4 to Column 12
Column 1 Column 2 Column 3 Column 4 Column 5 Column 6 Column 7 Column 8 Column 9 Column 10 Column 11 Column 12
Day Month LOB ABC Aux BCD Aux CDE Aux DEF Aux EFG Aux FGH Aux GHI Aux HIJ Aux IJK Aux
2/15/17 Feb CE LAB 12 14 45 33 21 78 102 11 8
2/15/17 Feb CE LAB 4 22 54 49 35 65 200 56 21
2/15/17 Feb CE LAB 4 22 54 49 35 65 200 56 21
2/15/17 Feb CE LAB 4 22 54 49 35 65 200 56 21
2/19/17 Feb CE LAB 4 22 54 49 35 65 200 56 21
2/19/17 Feb BA LAB 99 102 522 644 621 687 455 255 333
3/12/17 Mar BA LAB 145 122 43 989 477 344 56 89 9
3/12/17 Mar BA LAB 145 122 43 989 477 344 56 89 9
3/12/17 Mar BA LAB 145 122 43 989 477 344 56 89 9
3/17/17 Mar BA LAB 145 122 43 789 77 44 6 9 9
3/17/17 Mar BA LAB 145 122 43 98 47 4 5 7 8
3/19/17 Mar BA LAB 145 122 43 989 477 344 5 89 9
4/15/17 Apr CE LAB 4 22 54 49 35 65 200 56 21
4/15/17 Apr CE LAB 4 22 54 49 35 65 200 56 21
4/19/17 Apr BA LAB 145 122 43 989 477 344 56 89 9
4/19/17 Apr BA LAB 145 122 43 989 477 344 56 89 9


Looking forward to your help on this and many thanks in advance

 

[Aladin Akyurek]

Care to re-post the sample from Sheet2 using one of the possible methods listed in https://www.mrexcel.com/forum/about-...ml#post2507729?

Try to also provide a desired result for a LOB interest and a sub LOB of interest, manually calculated from the sample (By the way, no need to talk in terms of dropdown lists as the choices from them are simply conditions/criteria the formula(s) must refer to.).

 

[fritzkie_alexander]

Hi Aladin,

Hope this helps, the data source or sheet2 copy your requesting is save as sheet2 copy. I also saved an edited copy of sheet 1 save as master template to give you the full picture of my file.

https://www.dropbox.com/s/0prxthwzx5tjxqu/sheet 2 sample.xlsx?dl=0 Sheet2

https://www.dropbox.com/s/cymez4xa6w1zn1a/Master Template.xlsb?dl=0 Master Template


Many Thanks!!!
Fritz