Calculating Averages with Multiple Criteria

[Eustace07]

Hello

I'm trying to create a template to calculate TotalSpend and AverageSpend for various staff expenditure . The data is fairly large, over 20,000 rows so I need a robust formula to automate the process at best. A macro will make my day!! I tried using pivot, however i couldn't arrange the table in the manner I want to present it as per the attached so I decided to play it safe with formulas instead. I want to calculate the following:

Spend for each expense type
Average spend per staff for each expense type
Spend for each expense type as per Taxable code
Spend for each expense type as per non-Taxable code

NOTE: there are different forms of Entertaining.

I've calculated the TotalSpend, however, I'm not sure how to go about the averages. Any ideas as to how to go calculate this or automate the spreadsheet will much appreciated?

Many thanks

[Gregb11]

If we just look at the section for GF1226, Air Travel, Flight, you should be able to adapt for everything else.

First, I don't know if it's more efficient or not, but for me it's simpler, so another option to get the Total Spend would be to enter this formula in C73:
=SUMIFS(Sheet1!$G$2:$G$20,Sheet1!$B$2:$B$20,$A73,Sheet1!$F$2:$F$20,$A$69,Sheet1!$C$2:$C$20,B$70,Sheet1!$D$2:$D$20,B$71)

Either way, in Cell B73, to get the AverageSpend, try:
=C73/COUNTIFS(Sheet1!$B$2:$B$20,$A73,Sheet1!$C$2:$C$20,B$70,Sheet1!D2:D21,B$71,Sheet1!F2:F21,A$69)

[Gregb11]

I posted a little in haste - I think some of the formulas can be tweaked (with absolute references or not), but hopefully it gives you the idea.

[Eustace07]

Many thanks Gregb11. This works fine! Much appreciated.