Filter, count and sum in Excel 2016

Question

In the data set above, instructors were surveyed to learn what are the best and worst workout according to them. Using the sum and countifs together we realise that 2 yoga instructors believe that Yoga is the best form of workout, whereas a swimming instructor (Jaime) believes that swimming is a Poor form of workout.

I would now like to filter the data-set by age of response and then aggregate the results of the survey. If the response is New, then SUM(CountIfs(BestWorkout,"[workout text]", Instructing, "[workout text]")

Thanks for your help!!!

Answer 1

Use array formula instead of COUNTIFS():

=SUMPRODUCT(IF(IF($A$3:$A$6="new",IF(INSTRUCTING_IN=$B14,BEST_WORKOUT,""),"")=$B14,1,0))

After putting this formula, press CTRL+SHIFT+ENTER, then fill down