Calculating average and total time for a category in another worksheet

[leilah]

Hello forum,
I am really struggling to calculate two values:
(1) Average Time Spent for a category
(2) Total Time Spent for a category

For both of these the values are in another worksheet, containing
column a: event start date time
displays as 11/3/17 10:18
column b: event stop date time
displays as 11/3/17 14:52
column c: category name

Failed Attempt to Calculate Total Time Spent for the "Salad" Category:
=SUMIFS(Sheet1!C13150:Sheet1!C13944-Sheet1!B13150:Sheet1!B13944,"Salad",Sheet1!G13150:Sheet1!G13944)

I do not have an attempt for the average time, since the total time part is not yet working.

I would be very grateful if some kind soul can help me here.

- Leila

 

[barry houdini]

Hello leilah, welcome to MrExcel,

SUMIFS doesn't allow you to do calculations within the formula, perhaps have one SUMIF for the total end times and subtract another SUMIF with the start times, e.g.

=SUMIF(Sheet1!G13150:G13944,"Salad",Sheet1!C13150:C13944)-SUMIF(Sheet1!G13150:G13944,"Salad",Sheet1!B13150:B13944)

Once you have the total then the average should just be that total divided by a count of "Salad", so if the above formula is in H2 then for average try

=H2/COUNTIF(Sheet1!G13150:G13944,"Salad")

 

[leilah]

Barry that worked perfectly - thank you!!!