Need help creating a simple (?) SUM formula!

S

sandres74

Here's a spreadsheet that I need to make 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.

Here's my question:
If I want to have the sum of all sales made ONLY on Mondays for the entire
year to appear in a cell (let's say cell F1), what would the formula have to
be?
In other words, how can I get Excel to calculate the sum of ONLY the numbers
listed in column C which have the word 'Monday' written in the same row of
column B?

I hope I explained that clearly! Thanks in advance!
 
M

Ms-Exl-Learner

Paste this formula in F1 cell
=SUMIF(B:B,"MONDAY",C:C)

Remember to Click Yes, if this post helps!
 
J

Jacob Skaria

You dont need Column B to get that. Use the function WEEKDAY() with
SUMPRODUCT() to get that

=SUMPRODUCT((WEEKDAY(A1:A100)=2)*C1:C100)
 
S

sandres74

That worked great! Thanks!
Now what if I want to have a cell (let's say cell G1) that gives me the
average of all of those Monday sales for the year, what would the formula
have to be? (Ideally that formula would be able to ignore zeros as well as
blank cells)

Thanks again!
 
M

Ms-Exl-Learner

Just now i have seen your reply and the formula you have asked is given below.

Use this below formula in G1 cell:-

=SUMIF(B:B,"MONDAY",C:C)/SUMPRODUCT((B1:B65535="MONDAY")*(C1:C65535<>0)*(C1:C65535<>""))

Which will ignore the zero value and blank cells in C column when
calculating the Average for Monday.
 

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

Top