Forum Discussion

Jay Lea's avatar
Jay Lea
Copper Contributor
Dec 10, 2019
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...
excel
Formulas and Functions
  • Jay Lea's avatar
    Jay Lea
    Dec 12, 2019

    Subodh_Tiwari_sktneer 

     

    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!! 

Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Dec 12, 2019

Jay Lea 

Like this...

In D2

=SUMPRODUCT(IFERROR((COUNTIF($G$1:$S$1,data!$C$1:$CF$1)*
INDEX(data!$C$2:$CF$65,MATCH($A2,data!$B$2:$B$65,0),0))/
SUMPRODUCT(COUNTIF($G$1:$S$1,data!$C$1:$CF$1*
(INDEX(data!$C$2:$CF$65,MATCH($A2,data!$B$2:$B$65,0),0)<>""))),0))

In E2

=SUMPRODUCT(IFERROR((COUNTIF($G$1:$S$1,data!$C$1:$CF$1)*
INDEX(data!$C$2:$CF$65,MATCH($A2,data!$B$2:$B$65,0),0))/
SUMPRODUCT(COUNTIF($G$1:$S$1,data!$C$1:$CF$1*
(INDEX(data!$C$2:$CF$65,MATCH($A2,data!$B$2:$B$65,0),0)<>""))),0))
Jay Lea's avatar
Jay Lea
Copper Contributor
Dec 12, 2019

Subodh_Tiwari_sktneer 

 

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!! 

  • Jay Lea's avatar
    Jay Lea
    Copper Contributor
    Dec 14, 2019

    Twifoo 

     

    My greatest apologies...  you deserve all the credit for the most efficient and best way to find the averages. It was an oversight on my part. Thank you for all of your help. 

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Dec 13, 2019
    I’m glad you did! Nonetheless, I aver that my recommended formulas were crucial. Clearly, what you claim to have done actually came from my recommended formulas! I’m just sad you didn’t accord me any recognition for that. Worse, you simply ignored my explanation to your queries. Hopefully, you realize your sheer inadvertence, honest mistake, or excusable negligence, any of which I hereby expressly condone.

Resources