Forum Discussion
Solved
best way to find max and average values for data that meets certain criteria
I have a table with names in column B and dates in row 1. The rest of the tables is the sales amount for the employee listed in column B on the date in row 1. I want to find the max and the avera...
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!!
You may try this...
In C2
=MAX(MAXIFS(INDEX(data!$C$2:$N$13,MATCH($A2,data!$B$2:$B$13,0),),data!$C$1:$N$1,report!$M$1:$P$1))
In D2
=MAX(MAXIFS(INDEX(data!$C$2:$N$13,MATCH($A2,data!$B$2:$B$13,0),),data!$C$1:$N$1,"<>"&report!$M$1:$P$1))
The one for C2 worked perfectly, but the one for D2 did not. The "<>"& doesn't give an error but it doesn't seem to be working. You are doing it they way I wanted to do it. I wasn't sure how to do an index match with maxifs, but it is working. Why does the <> work with an array?
Anyone know the answer to this?