# Count cells based on formula results Dates vs Currency

1. ## 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

2. ## Re: Count cells based on formula results Dates vs Currency

The attachment is protected by password.

3. ## Re: Count cells based on formula results Dates vs Currency

Argh, I tried to remove it.
PW: 1234

Thanks

4. ## 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

5. ## 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. ## 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.

#### Thread Information

##### Users Browsing this Thread

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

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