sumifs with ranges and criterias depending on an if statement

[alexcrofut]

Hello,
I am working on an interactive table
Depending on drop down menus, the values of a table are supposed to change and sum different rows of data.

=SUMIFS('Sheet1'!$X:$X,'Sheet1'!$AR:$AR,report!$F$5,'Sheet1'!Q:Q,report!D33,'Sheet1'!$Q:$Q,Sheet1!B35,)

want to add another criteria and range which depend on drop a down menu outcomes 1,2,3
if 1 look for criteria in column $A
if 2 look for criteria in column $B
if 3 look for criteria in column $c
(criteria stays the same)

How could I integrate this into the sumifs formula?

Thanks

[JBeaucaire]

Can you clarify more specifically in reference to the sample SUMIFS() formula you've already provided:

1) Where will this drop down be, exactly?
2) Which of the elements of the existing SUMIFS() is supposed to change based on the drop down, exactly?

[alexcrofut]

hello,
Sorry I realize I'm not very clear. Here is the full equation i'm using right now which works but (obviously due to its size) takes a lot of time to process.

the drop down tables are in B9 and B5 (B5 gives you the option of looking at individual levels or ALL combined)

=IF($B$9="By Billing date-Monthly",IF($F$5="ALL",SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$AU:$AU,report!D$32),SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$AR:$AR,$F$5,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$AU:$AU,report!D$32)),IF($B$9="By Payment Date- Monthly",IF($F$5="ALL",SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$Z:$Z,report!D$32),SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$AR:$AR,$F$5,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$Z:$Z,report!D$32)),IF($B$9="By DOS- Monthly",IF($F$5="ALL",SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$F:$F,report!D$32),SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$AR:$AR,$F$5,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$F:$F,report!D$32)),IF($B$9="By Payment date- Quarterly",IF($F$5="ALL",SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$AA:$AA,report!D$32),SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$AR:$AR,$F$5,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$AA:$AA,report!D$32)),IF($B$9="By Billing date-Quarterly",IF($F$5="ALL",SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$AV:$AV,report!D$32),SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$AR:$AR,$F$5,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$AV:$AV,report!D$32)),IF($B$9="By DOS- Quarterly",IF($F$5="ALL",SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$G:$G,report!D$32),SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$AR:$AR,$F$5,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$G:$G,report!D$32))))))))

I'm really just trying to find a way to simplify it without inbedding a bunch of if statements

[JBeaucaire]

As long as the formula actually fits, I have to say at least once that your formula at least has the benefit of people READABLE and very easily edited, once you add in the line feeds that might make it read easier. Try entering this same formula as shown here:

=IF($B$9="By Billing date-Monthly",
 IF($F$5="ALL",SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$AU:$AU,report!D$32),
               SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$AR:$AR,$F$5,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$AU:$AU,report!D$32)),
 IF($B$9="By Payment Date- Monthly",
 IF($F$5="ALL",SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$Z:$Z,report!D$32),
               SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$AR:$AR,$F$5,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$Z:$Z,report!D$32)),
 IF($B$9="By DOS- Monthly",
 IF($F$5="ALL",SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$F:$F,report!D$32),
               SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$AR:$AR,$F$5,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$F:$F,report!D$32)),
 IF($B$9="By Payment date- Quarterly",
 IF($F$5="ALL",SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$AA:$AA,report!D$32),
               SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$AR:$AR,$F$5,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$AA:$AA,report!D$32)),
 IF($B$9="By Billing date-Quarterly",
 IF($F$5="ALL",SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$AV:$AV,report!D$32),
               SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$AR:$AR,$F$5,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$AV:$AV,report!D$32)),
 IF($B$9="By DOS- Quarterly",
 IF($F$5="ALL",SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$G:$G,report!D$32),
               SUMIFS('ASRAPPL-raw'!$X:$X,'ASRAPPL-raw'!$AR:$AR,$F$5,'ASRAPPL-raw'!$Q:$Q,$B34,'ASRAPPL-raw'!$G:$G,report!D$32))))))))

I'm not sure I would actually try to shorten this formula, the result would just a slow if not slower on a large dataset... SUMIFS() are doing a lot of work, line by line so it takes as long as it takes.