Forum Discussion

ALLENCANADA's avatar
ALLENCANADA
Copper Contributor
Jan 30, 2022
Solved

How to Average Sales Data based on a matching date

How to Average Sales Data based on a matching date

Good morning all! Hello from Ontario Canada!

I've got a multi data sheet here and I'm trying to get the worksheet to calculate a gross average based on a Avereages by datedate selected from a drop down menu and data from another sheet.

As you can see, 'Deal Tracking' carries plenty of data entries, all starting with a date. I'd like drop down in B2 of 'B-SHEET' to select a date and for the worksheet to pull all the sales number from the 'R' column of Deal Tracking and automatically average out based on the amount of results returned for that date and display it in 'B-SHEET' B7.

I'm scraping my head over this one even though I'm sure I'm close to solving it, I've decided to search for some help!

File Located here: https://www.dropbox.com/scl/fi/1zus5r5im4vmtbdsi4e97/BH-Performance-Tracker-January-COPY-22.xlsx?dl=0&rlkey=1qkqk3cff1ds5v0nte1m3850w


All help is appreciated!

Allen.
Excel on Mac
office 365

8 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jan 30, 2022

    ALLENCANADA You should AVERAGEIF in B7 as demonstrated in the picture below. Not sure what you wan tin the other cells as the just show REF errors.

     

    • ALLENCANADA's avatar
      ALLENCANADA
      Copper Contributor
      Jan 30, 2022

      Riny_van_Eekelen 

      Thanks so much! This is definitely what I'm after, I forgot to mention that I also wanted to sort by 'Deal Tracking' D7-D300 "NEW".

       

      I'm trying to change it to AVERAGEIFS but it's not accepting the additional criteria 😞

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Jan 30, 2022

        ALLENCANADA You indeed need AVERAGEIFS.

         

        Try this:

        =AVERAGEIFS('Deal Tracking'!R7:R300,'Deal Tracking'!B7:B300,B2,'Deal Tracking'!D7:D300,"New")

Resources