Jan 30 2022 06:40 AM - edited Jan 30 2022 06:51 AM
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=...
All help is appreciated!
Allen.
Jan 30 2022 07:02 AM
What is 'PVR NEW', 'NEW F&I' etc.?
Jan 30 2022 07:12 AM
@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.
Jan 30 2022 08:57 AM
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 :(
Jan 30 2022 09:04 AM
Try
=AVERAGEIFS('Deal Tracking'!R7:R300, 'Deal Tracking'!B7:B300, B2, 'Deal Tracking'!D7:D300, "NEW")
Note that the range to average is now the first argument.
Jan 30 2022 09:08 AM
@ALLENCANADA You indeed need AVERAGEIFS.
Try this:
=AVERAGEIFS('Deal Tracking'!R7:R300,'Deal Tracking'!B7:B300,B2,'Deal Tracking'!D7:D300,"New")
Jan 30 2022 09:32 AM
Jan 30 2022 02:56 PM
@Riny_van_Eekelen Thanks so much my friend, that's perfect!