Average based on multiple criteria

Hi everyone,

I`m working on an evaluation sheet for tour guides at a medium sized tour company. At the end of each trip all customers are asked to fill in a short customer satisfaction research and rate different parts of the tour on a scale of 1 to 5.

All survey results are entered in an Excel sheet with the following columns:

Column A: Tour date
Column B: Destination
Column C: Guide
Column D - J: Results for organization, knowledge, friendliness etc.

Sheet 2 gives an overview of the results and the average score for each guide. However, most guides guide tours to different destinations, so now I like to calculate the results for each individual tour.

This is my formula to calculate the average score from one guide for all tours:
=AVERAGE(IF(Questionaires!$C$2:$C$5000="Bob",Questionaires!$D$2:$D$5000))

How can I modify this formula so it calculates the average from column D for only the tours to Vancouver (column B), guided by Bob(column C), and excluding the empty cells in column D?

Thanks!

Try:

Please Login or Register  to view this content.

I also like to display the number of entries for trips to Vancouver guided by Bob

I got the following formula, but how do I now filter the results to only include the Vancouver trips from column B?

=COUNTIF(Questionaires!C:C,"Bob")

Secondly, column L contains the answers to the question "Would you recommend this tour?" (yes/no)
I would like to calculate for each individual tour how many percent of the people answered this question with "yes"

=COUNTIF(Questionaires!L:L,"Yes")/COUNTA(Questionaires!L:L) calculates the percentage for all tours, but how do I exclude everything but "Vancouver"?

Thanks a lot for your help!

Hello Bob,

You can use SUMPRODUCT to count with multiple criteria in Excel 2003, e.g. this formula to count Bob's Vancouver tours

=SUMPRODUCT((Questionaires!C$2:C$5000="Bob")*(Questionaires!B$2:B$5000="Vancouver"))

Then using the same setup for the numerator you can divide the number of Vancouver tours that are recommended by the total number of Vancouver tours like this

=SUMPRODUCT((Questionaires!L$2:L$5000="Yes")*(Questionaires!B$2:B$5000="Vancouver"))/MAX(1,COUNTIF(Questionaires!B$2:B$5000,"Vancouver"))

[MAX stops you getting an error if there are no Vancouver tours]

....or if you want to get in to the murky world of array formulas (and who wouldn't? ) you can try this version for the same result

=AVERAGE(IF(Questionaires!B$2:B$5000="Vancouver",IF(Questionaires!L$2:L$5000="Yes",1,0)))

confirmed with CTRL+SHIFT+ENTER

So you have another criteria on a different sheet? I am not sure that would work out.;... can you explain further?

Try:

=AVERAGE(IF(Questionnaires!$C$2:$C$4925="Jordan",Questionnaires!$E$2:$E$4925),IF(Rockies!$C$2:$C$4925="Jordan",Rockies!$E$2:$E$4925))

confirmed with CTRL+SHIFT+ENTER not just ENTER... assumes same range locations and sizes in both sheets... adjust as necessary.

=COUNTIF(Questionaires!C:C,"Bryson")+COUNTIF(Rockies!C:C,"Bryson")

=AVERAGE(Questionaires!E2:E4925,Rockies!E2:E4925)