+ Reply to Thread
Results 1 to 12 of 12

Year average across multiple worksheets with one criteria

  1. #1
    Registered User
    Join Date
    02-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 365 version 2202
    Posts
    29

    Year average across multiple worksheets with one criteria

    In a workbook there are worksheets named North, South, East, West, and Stats. All four worksheets are formatted the same and have the same columns. Column A contains the date of inquiry. Column Y contains the number of days for processing the inquiry. On the Stats worksheet one of the stats is a monthly breakdown of other data. In column B of the Stats worksheet the numeric date for the first of each month is entered in a cell which has a custom format of MMM. The Stats worksheet contains data broken out by year for a two year period. The formula needed is to have a year average of all four worksheets for the number of days to process the inquiries with in a specific year. The formula will appear on the Stats worksheet. The formula can reference one of the cells in column B on the Stats worksheet to identify what year the stats are to be collected for.

    The desired formula will reference a cell in column B of the Stats worksheet to identify which year to search for. Then search column A of the four worksheets and identify the dates that fall with in the identified year and sum the number of days to process the inquiry which is contained in Column Y. I have been unable to write the formula properly.

    Your assistance is greatly appreciated.

    1/5/18 - Prior to posting this request I had previously searched the internet for a similar formula that I could adapt but I did not find one.
    Last edited by Tryin2Excel; 01-05-2018 at 12:46 PM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Year average across multiple worksheets with one criteria

    As all your sheets are the same format
    Google "Excel 3d SUM".
    See if that helps
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    02-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 365 version 2202
    Posts
    29

    Re: Year average across multiple worksheets with one criteria

    I tried to be very clear. If I haven't please feel free to ask any clarifying questions.

    Thank you for your willingness to help.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Year average across multiple worksheets with one criteria

    Special-K has suggested that you do a Google search using the search term "Excel 3D SUM", because this is a common requirement and there is a lot of data out there. Have a go - if you find a formula, but can't make it work, then come back and we can help you to implement it. Similarly if you can't find what you need.

    I hope this is clear.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    02-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 365 version 2202
    Posts
    29

    Re: Year average across multiple worksheets with one criteria

    Thank you to both of you for your suggestion. I have searched the internet yesterday and part of this morning trying to find a formula to adapt and have not been able to. The difficult part in writing the formula is how to reference a date in column B on the Stats worksheet and have the formula extract the year from that date and look for the dates that fall into the same year and sum the days to process in column Y.

    On the Stats worksheet I have a similar formula to count the number of times something appeared based on the year. =SUMPRODUCT(--(TEXT(North!$A$3:$A$600,"YYY")=TEXT($B5,"YYY"))*((North!$J$3:$J$600="Website")*(ISTEXT(North!$C$3:$C$600)))) In this formula Column A on the North sheet contains the date of inquiry. B5 on the Stats sheet contains 1/1/2017 but with the custom format of MMM. Column J on the North sheet contains information of the referring source. And the (ISTEXT(North!$C$3:$C$378)) is a "safety measure" to insure all the correct data was entered on the row. I had found this from an internet search and was able to adapt it. I wrote this formula for each work sheet and added the four together for the results.

    So my hope was to find something similar on the internet that I could adapt but I haven't been able to.

    Thanks.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Year average across multiple worksheets with one criteria

    In that case, please provide us with a sample workbook.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

  7. #7
    Registered User
    Join Date
    02-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 365 version 2202
    Posts
    29

    Re: Year average across multiple worksheets with one criteria

    Thank you. I am attempting to attached a sample workbook per AliGW's instructions. Please let me know if it is not attached.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 365 version 2202
    Posts
    29

    Re: Year average across multiple worksheets with one criteria

    In checking the workbook did attach. Thank you AliGW for the instructions. If anyone is able to assist with the formula I would appreciate it. Details are provided in my two posts in this thread.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Year average across multiple worksheets with one criteria

    Perhaps this will be of some help. The directional sheets now contain tables like the ones on sheet 5. Column AA of the tables is populated by the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    On sheet 5 the counts for the various months are made using the 3d drill application of the SUM function: =SUM(North:West!AB4)
    If you need help with other stats please manually provide those numbers so that we can attempt to write formulas or code to replicate the numbers.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  10. #10
    Registered User
    Join Date
    02-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 365 version 2202
    Posts
    29

    Re: Year average across multiple worksheets with one criteria

    Thank you very much for your suggestion. I certain there is a way to write a sumproduct that will not require the added columns in AA to AH for each worksheet.

    I have made one modification on the Stats worksheet where "Dec" is entered as 12/31 so my formula can reference that cell for the last day of the year and the cell containing "Jan" for the first day of the year. With that I wrote a very long formula on the Stats worksheet using a SUMSIF with criteria for each worksheet and added those together. Then used a SUMPRODUCT with criteria for each worksheet and added those together. Then divided the two results for the average. It of course is a large formula =SUMIFS(Midtown!$Y$3:$Y$600,Midtown!$A$3:$A$600,">="&$B$5,Midtown!$A$3:$A$600,"<="&$B$16)+SUMIFS(Alberta!$Y$3:$Y$600,Alberta!$A$3:$A$600,">="&$B$5,Alberta!$A$3:$A$600,"<="&$B$16)+SUMIFS(Gresham!$Y$3:$Y$600,Gresham!$A$3:$A$600,">="&$B$5,Gresham!$A$3:$A$600,"<="&$B$16)+SUMIFS(East!$Y$3:$Y$600,East!$A$3:$A$600,">="&$B$5,East!$A$3:$A$600,"<="&$B$16)/SUMPRODUCT(--(TEXT(Midtown!$A$3:$A$600,"YYY")=TEXT($B$5,"YYY"))*((ISNUMBER(Midtown!$Y$3:$Y$600))*(ISTEXT(Midtown!$C$3:$C$600))))+SUMPRODUCT(--(TEXT(Alberta!$A$3:$A$600,"YYY")=TEXT($B$5,"YYY"))*((ISNUMBER(Alberta!$Y$3:$Y$600))*(ISTEXT(Alberta!$C$3:$C$600))))+SUMPRODUCT(--(TEXT(Gresham!$A$3:$A$600,"YYY")=TEXT($B$5,"YYY"))*((ISNUMBER(Gresham!$Y$3:$Y$600))*(ISTEXT(Gresham!$C$3:$C$600))))+SUMPRODUCT(--(TEXT(East!$A$3:$A$600,"YYY")=TEXT($B$5,"YYY"))*((ISNUMBER(East!$Y$3:$Y$600))*(ISTEXT(East!$C$3:$C$600))))

    I do appreciate your suggestion. Thanks again.

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Year average across multiple worksheets with one criteria

    If all the individual sheets have the same columns, I would strongly recommend that you combine them into one sheet, with an added column to indicate the original source. It will make your life much, much easier when it comes to reporting at an aggregate level.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  12. #12
    Registered User
    Join Date
    02-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 365 version 2202
    Posts
    29

    Re: Year average across multiple worksheets with one criteria

    Thank you xlnit wit. I agree it would make life so much easier for me. But because they are all inexperienced in working in Excel and needing a shared workbook so several people can enter data at the same time it is best to have the individual worksheets.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] sumifs with multiple criteria and date range year
    By Gijs in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-24-2017, 02:08 AM
  2. Average based on criteria and multiple criteria
    By batchjb69 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-05-2016, 04:30 AM
  3. Replies: 1
    Last Post: 02-15-2016, 04:35 PM
  4. Trying to find median & average for multiple columns with multiple criteria
    By help-meplease in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-11-2016, 06:59 PM
  5. Average numbers from multiple columns based on multiple criteria
    By abambi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-23-2015, 01:52 PM
  6. Getting an average across multiple worksheets
    By kd5649 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-26-2014, 06:34 AM
  7. Replies: 5
    Last Post: 06-27-2012, 09:27 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1