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

[MTooba]

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 data from sheet 1 and 2. Is there any way to combine the results of different categories in the final sheet?

Thank you very much for taking the time.

Best regards,

Tooba

[#2 · Accepted solution]

@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

[#3]

Thank you very much. It is really helpful. The formula with C6:C10 works well.

[#4]

@mtarler 

Hi, its me again. Hope you are doing well.
Please check the attached file. I need to fix the price of one task from a list for all the staff members this time. Previously, the total was calculated according to staff's rate per hour, but now I need to include one task at fixed price for all the staff members.

Can you please have a look if it can be done?

Thank you very much for your time.

Best regards,

Tooba

[#5]

@MTooba I'm sure it is possible if enough information is known.  Right now you have a table with person and rate, and a table with person, hours, tasks and $$ based on previous table.  You want to fix the rate for a given task but you don't have a table that defines rate for a given task.  You also want person to get their rate for 'other' tasks but don't define what that is.  For example this excerpt from the table:

Staff	Task	# Hrs	Total
Jerry	Janitorial	4	440
Mac	Janitorial	12	420
Julia	Janitorial	3	240

each of these staff do Janitorial and I think you want to 'fix' the rate but at what amount?  Each of them have different rates.   

[#6]

@mtarler Thank you for getting back to me. 

You are right. That is exactly the issue. Tasks do not have rates. All the payments are made on the basis of employee hourly rate regardless of what they do.

Now, admin has decided that one of the tasks will be billed on the basis of task's price and not according to staff's hourly rate.

Let's continue with the example of "Janitorial" from the excerpt. I want to assign a value "300" to janitorial tasks, no matter who is performing. In this case hourly rates of staff will not apply. (So this particular task will be an exception).

For other tasks, there are no rates, but the calculations are made on the basis of hourly rate of the person performing the task.

Kindly let me know if I have explained it properly.

[#7]

@MTooba so yes it is possible but is that the only task with a fixed rate or just 1 example?  The basic concept I see is:    lookup(based on task) but if error then lookup(based on staff rate) 

So you need a table of tasks and corresponding rates then if the lookup fails to find the task then it will perform the lookup we did for the staff.

[#8]

@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?

[#9]

@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))

[#10]

@mtarler  

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

Best regards,

Tooba

[#11]

@mtarler  This was really helpful. Thank you very much.