how to simplify nesting formula

[Rhon Alvarez]

Hi Team,

Is there any way we can simplify a nested formula like this one without using any VBA? this was just a repetitive sum of data from 3 columns divided by another column, however, the trick is: this was on a daily basis, per date, and with different skills listed on the formula such as "229", "408", "405" "415" and so on.... total of 14 skills per day. Apologies but I could not attach the file for it was a little bit big.




>> Use Code -tags <<

'=IFERROR((SUMIFS('from BSR v2 Daily'!$AT:$AT,'from BSR v2 Daily'!$AP:$AP,"229",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AU:$AU,'from BSR v2 Daily'!$AP:$AP,"229",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AV:$AV,'from BSR v2 Daily'!$AP:$AP,"229",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AT:$AT,'from BSR v2 Daily'!$AP:$AP,"408",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AU:$AU,'from BSR v2 Daily'!$AP:$AP,"408",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AV:$AV,'from BSR v2 Daily'!$AP:$AP,"408",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AT:$AT,'from BSR v2 Daily'!$AP:$AP,"405",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AU:$AU,'from BSR v2 Daily'!$AP:$AP,"405",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AV:$AV,'from BSR v2 Daily'!$AP:$AP,"405",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AT:$AT,'from BSR v2 Daily'!$AP:$AP,"415",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AU:$AU,'from BSR v2 Daily'!$AP:$AP,"415",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AV:$AV,'from BSR v2 Daily'!$AP:$AP,"415",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AT:$AT,'from BSR v2 Daily'!$AP:$AP,"908",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AU:$AU,'from BSR v2 Daily'!$AP:$AP,"908",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AV:$AV,'from BSR v2 Daily'!$AP:$AP,"908",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AT:$AT,'from BSR v2 Daily'!$AP:$AP,"221",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AU:$AU,'from BSR v2 Daily'!$AP:$AP,"221",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AV:$AV,'from BSR v2 Daily'!$AP:$AP,"221",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AT:$AT,'from BSR v2 Daily'!$AP:$AP,"416",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AU:$AU,'from BSR v2 Daily'!$AP:$AP,"416",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AV:$AV,'from BSR v2 Daily'!$AP:$AP,"416",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AT:$AT,'from BSR v2 Daily'!$AP:$AP,"419",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AU:$AU,'from BSR v2 Daily'!$AP:$AP,"419",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AV:$AV,'from BSR v2 Daily'!$AP:$AP,"419",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AT:$AT,'from BSR v2 Daily'!$AP:$AP,"434",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AU:$AU,'from BSR v2 Daily'!$AP:$AP,"434",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AV:$AV,'from BSR v2 Daily'!$AP:$AP,"434",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AT:$AT,'from BSR v2 Daily'!$AP:$AP,"482",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AU:$AU,'from BSR v2 Daily'!$AP:$AP,"482",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AV:$AV,'from BSR v2 Daily'!$AP:$AP,"482",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AT:$AT,'from BSR v2 Daily'!$AP:$AP,"439",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AU:$AU,'from BSR v2 Daily'!$AP:$AP,"439",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AV:$AV,'from BSR v2 Daily'!$AP:$AP,"439",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AT:$AT,'from BSR v2 Daily'!$AP:$AP,"440",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AU:$AU,'from BSR v2 Daily'!$AP:$AP,"440",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AV:$AV,'from BSR v2 Daily'!$AP:$AP,"440",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AT:$AT,'from BSR v2 Daily'!$AP:$AP,"816",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AU:$AU,'from BSR v2 Daily'!$AP:$AP,"816",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AV:$AV,'from BSR v2 Daily'!$AP:$AP,"816",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AT:$AT,'from BSR v2 Daily'!$AP:$AP,"817",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AU:$AU,'from BSR v2 Daily'!$AP:$AP,"817",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AV:$AV,'from BSR v2 Daily'!$AP:$AP,"817",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22))/(SUMIFS('from BSR v2 Daily'!$AR:$AR,'from BSR v2 Daily'!$AP:$AP,"229",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AR:$AR,'from BSR v2 Daily'!$AP:$AP,"408",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AR:$AR,'from BSR v2 Daily'!$AP:$AP,"405",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AR:$AR,'from BSR v2 Daily'!$AP:$AP,"415",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AR:$AR,'from BSR v2 Daily'!$AP:$AP,"908",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AR:$AR,'from BSR v2 Daily'!$AP:$AP,"221",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AR:$AR,'from BSR v2 Daily'!$AP:$AP,"416",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AR:$AR,'from BSR v2 Daily'!$AP:$AP,"419",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AR:$AR,'from BSR v2 Daily'!$AP:$AP,"434",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AR:$AR,'from BSR v2 Daily'!$AP:$AP,"482",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AR:$AR,'from BSR v2 Daily'!$AP:$AP,"439",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AR:$AR,'from BSR v2 Daily'!$AP:$AP,"440",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AR:$AR,'from BSR v2 Daily'!$AP:$AP,"816",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)+SUMIFS('from BSR v2 Daily'!$AR:$AR,'from BSR v2 Daily'!$AP:$AP,"817",'from BSR v2 Daily'!$AK:$AK,IBOR!$B22)),"")

Thanks in advance for the help!

 

[pecoflyer]

Post a sample sheet please (pics are useless) (even with some mock up data)

 

[GraH - Guido]

Perhaps, you can work with an intermediate helper table that contains the sumifs. On that table build the new sums?
Or combine data tables with a PowerQuery solution. Would be needing a sample workbook. Reduce therefore the number of data lines in the worksheet. (+ anonymize it)

 

[Peter Bartholomew]

Would something like

= SUMPRODUCT(
        SUMIFS(ACD,split,values,date,target)+
        SUMIFS(hold,split,values,date,target)+
        SUMIFS(ACW,split,values,date,target))
    /
    SUMPRODUCT(
        SUMIFS(calls,split,values,date,target))

be easier to read?

I have used defined names rather than direct cell references, which shortens the formula and adds meaning. The other change is that your 'magic numbers' have been placed within a 'values' array (either read from a range or defined as a named constant). Since the SUMIFS then give an array of results, further SUMPRODUCTs are used to sum the arrays.

 

[p45cal]

You could do this with a pivot table, then your formula could be reduced to the likes of:
=GETPIVOTDATA("Sum of Field1",$J$4,"Date",IBOR!B22,"Split/Skill2","Group1")
which you can copy down.

Where in the pivot table (sitting at cell J4) you:

• group your skill sets (Group1)
• have a calculated field (called Field1) being the sum of 3 columns divided by a fourth (= ('ACD Time' +'Hold Time' +'ACW Time' )/'ACD Calls')

If you upload a file with only the 2 or 3 needed sheets, with data reduction if necessary to be able to upload it, I'll add the necessary.

ps. the final formula could be as short as:
=GETPIVOTDATA("F",$J$4,"D",IBOR!B22,"S","G")

 

[Faseeh]

Just a comment, the length of this formula is 6065 characters that can be reduced by adopting shorter sheet name; replacing it from "from BSR v2 Daily" to name like "BSRV2" to get 4049 characters -This is a reduction of 33%. in formula's length.