+ Reply to Thread
Results 1 to 6 of 6

Count cells based on formula results Dates vs Currency

  1. #1
    Registered User
    Join Date
    03-12-2021
    Location
    Chicago
    MS-Off Ver
    2019
    Posts
    3

    Unhappy Count cells based on formula results Dates vs Currency

    Hello, I have created a dynamic calendar that references values on a separate sheet.
    Date rows (8,11,114,17,20,23) show the day of the month =DATEVALUE("1-"&A2&"-"&B2)-WEEKDAY(DATEVALUE("1-"&A2&"-"&B2),3)
    Sales rows (10,13,16,19,22,25) show the sales from Sales sheet and are dynamic based on the date =IF(DAY(B8)=1,(Sales!$C$10),IF((AND((DAY(B8)>1),(DAY(B8)<7))),OFFSET(Sales!$C$10,((DAY(B8)*10)-10),0),0))

    What I would like is to have a sales average for each day of the week. SUM of the 6 cells possible, then divided by the number of actual days in the month (Feb 2020 has 4 Mondays, but March has 5).

    I have tried using COUNT/IF/A/IFS/BLANK and AVERAGE/A/IF/IFS and can not figure out a way to do it. I Tried selecting the Range B8:B25 and selecting each of the 6 possible cells B10,B13,B16,B19,B22,B25
    Some fails:
    =COUNTIF((B10,B13,B16,B19,B22,B25),"<>0") = #VALUE!
    =COUNTIF((B8:B25),"<>0") = #VALUE!
    =AVERAGEA(B10,B13,B16,B19,B22,B25) = Wrong Result

    Any help would be great.
    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,459

    Re: Count cells based on formula results Dates vs Currency

    The attachment is protected by password.
    Quang PT

  3. #3
    Registered User
    Join Date
    03-12-2021
    Location
    Chicago
    MS-Off Ver
    2019
    Posts
    3

    Re: Count cells based on formula results Dates vs Currency

    Argh, I tried to remove it.
    PW: 1234

    Thanks

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: Count cells based on formula results Dates vs Currency

    Try


    in B27

    =AVERAGEIFS(B8:B25,$A$8:$A$25,"Sales",B8:B25,"<>0")

    Copy across
    Last edited by JohnTopley; 04-20-2021 at 01:52 PM.

  5. #5
    Registered User
    Join Date
    03-12-2021
    Location
    Chicago
    MS-Off Ver
    2019
    Posts
    3

    Thumbs up Re: Count cells based on formula results Dates vs Currency

    JohnTopley
    That worked!
    I would not have thought to use column A labels as the criteria.

    Thanks so much

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: Count cells based on formula results Dates vs Currency

    You're welcome.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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. If formula to show results based on the number of days between two dates
    By Arun7777 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-26-2020, 01:13 PM
  2. [SOLVED] Please Help, need a formula to count cells based on dates in 2 other cells matching.
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-18-2017, 03:20 AM
  3. [SOLVED] Formula that contains currency not returning the results as currency
    By Triscia in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 07-17-2014, 03:02 PM
  4. [SOLVED] Count cells containing Dates (greater than 2014) based on Filtered Cells
    By sam99 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-26-2014, 10:30 AM
  5. [SOLVED] Formula to count based on dates and text in multiple cells - See Spreadsheet
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-16-2013, 11:40 AM
  6. currency results for if then formula???
    By will2lewis71 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-26-2013, 01:53 PM
  7. Excel formula for filtering results based on dates
    By rhinohunter in forum Excel General
    Replies: 3
    Last Post: 06-13-2012, 04:03 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