Forum Discussion
best way to find max and average values for data that meets certain criteria
E2 had to be:
=SUMPRODUCT(IFERROR((COUNTIF($H$1:$S$1,data!$C$1:$CF$1)=0)*
INDEX(data!$C$2:$CF$65,MATCH($A2,data!$B$2:$B$65,0),0),0)/
SUMPRODUCT((COUNTIF($H$1:$S$1,data!$C$1:$CF$1)=0)*
(INDEX(data!$C$2:$CF$65,MATCH($A2,data!$B$2:$B$65,0),0)<>"")))I got it to work!!! Thank you everyone for all of the help!!
If your definition of best is "the shortest", I will recommend this formula in C2, as shown in the snapshot below:
Delving into the attached file, you'll see this formula in G2:
=SUMPRODUCT(COUNTIF($M$1:$P$1,data!$C$1:$N$1)*
INDEX(data!$C$2:$N$13,MATCH($A2,data!$B$2:$B$13,0),0))/
SUMPRODUCT(COUNTIF($M$1:$P$1,data!$C$1:$N$1*
(INDEX(data!$C$2:$N$13,MATCH($A2,data!$B$2:$B$13,0),0)<>"")))
Note that I used the elegant COUNTIF instead of the familiar ISNUMBER-MATCH combination.
- By admitting that my recommended formulas return your expected results, you must be contented thereby. Nonetheless, I admire your quest for a shorter formula but I deem my recommendation as heretofore the shortest!
The group of AVERAGE/IF/S cannot process array formulas! The most powerful among their “relatives” is the elegant COUNTIF, as you can prove in the file I attached earlier.