Forum Discussion

MTooba's avatar
MTooba
Brass Contributor
Nov 11, 2020
Solved

How to aggregate the drop down list items to get the category-wise sum?

Hi,   Please see the attached file. I am trying to calculate the costs in terms of categories (services) from drop down lists. Sheet 3 is the final sheet to show the calculated results based on the...
admin
BI & Data Analysis
Charting
developer
excel
Excel for web
Excel on Mac
Excel on mobile
Formulas and Functions
Macros and VBA
  • mtarler's avatar
    mtarler
    Nov 11, 2020

    MTooba in the attached I did what I think you want in 2 different ways:

    a) pivot table - this is powerful and flexible solution

    b) formula:

    =SUMIF(Sheet2!F:F,C6:C10,Sheet2!H:H)

    if the array formula above doesn't work then replace C6:C10 with just C6 and copy it down the column

MTooba's avatar
MTooba
Brass Contributor
Dec 19, 2020

mtarler yes. For now, it is only one task "transport" with fixed rate. 
So, let's say I add a table with all the tasks by adding the rate of other tasks as "0", and use Vlookup twice, it should help. I am understanding that I need to change the formula that was previously written.

Is that right?

 

 

mtarler's avatar
mtarler
Silver Contributor
Dec 20, 2020

MTooba Here is the formula:

 

=IF(E4="","",IFERROR(IFERROR(VLOOKUP(F4,Table3,2,0),VLOOKUP(E4,Table1,2,0)),0)*G4)

 

I also added the "AltTotal" column that uses the newer XLOOKUP() function:

=IF(E4="","",XLOOKUP(F4,Table3[Tasks],Table3[per hr rate],XLOOKUP(E4,Table1[Name],Table1[Per hour rate],0)*G4))
  • MTooba's avatar
    MTooba
    Brass Contributor
    Dec 22, 2020

    mtarler  This was really helpful. Thank you very much. 

  • MTooba's avatar
    MTooba
    Brass Contributor
    Dec 20, 2020

    mtarler  

     

    Thank you very much. I'll get back to you if I get stuck with something. Hope that is fine.

     

    Best regards,

    Tooba

Resources