I need help to make a not-so-easy AVERAGE formula!



Here's a description of spreadsheet that I am making for the sales of a store:

- Column A has all the dates of the year (January 1, January 2, January 3,
etc.) for the entire year listed one after another (so a total of 365 rows,
starting in row 1).
- Column B has the day of the week (Monday, Tuesday, Wednesday, etc.)
corresponding to the date next to it in column A.
- Column C has the sales amount (example: $125.50) corresponding to the
date/day listed in the same row of columns A and B.

I have the following formula (which works great) which gives me the SUM of
all sales made ONLY on MONDAYS for the entire year to appear in a cell:

My question is:
Is there a formula that would allow Excel to calculate the AVERAGE
(excluding blank cells and cells with zeros!) of all sales made ONLY on
MONDAYS for the entire
year? I have tried to modify the formula above to do that, but to no avail.
I know not what I am doing! Can anyone help?

Thanks in advance!


Use AVERAGEIFS(C:C,A:A,"Monday",C:C,">0"). If you do not have Excel 2007, use
SUMIF()/COUNTIF() together.

Gary''s Student

Since you already have the sum, you just need to divide by the count of the
non-zero, non-blank, values:


Mike H


This depends on PROPERLY formatted dates and to prevent the formula becoming
too long I used cell references for the criteria

D1= 1/1/2009
D2 = 31/12/2009
D3 = Monday
Note D3 is simply text, no formula
now to get the day of the week in column B I used in B1
and dragged down

Now the array formula. See below on how to enter an array formula

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Luke M

While the first formula is good, the second formula for earlier versions
doesn't ignore zero values. Could do:


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question