Need help creating a simple (?) SUM formula!
 

Here's a spreadsheet that I need to make for the sales of a sto

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

Need help creating a simple (?) SUM formula!
 

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

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"sandres74" wrote:

Here's a spreadsheet that I need to make for the sales of a sto

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

Need help creating a simple (?) SUM formula!
 

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

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

--
Jacob


"sandres74" wrote:

Here's a spreadsheet that I need to make for the sales of a sto

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

Need help creating a simple (?) SUM formula!
 

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!

"Ms-Exl-Learner" wrote:

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

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"sandres74" wrote:

Here's a spreadsheet that I need to make for the sales of a sto

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

Need help creating a simple (?) SUM formula!
 

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.

--------------------
(Ms-Exl-Learner)
--------------------


"sandres74" wrote:

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!

"Ms-Exl-Learner" wrote:

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

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"sandres74" wrote:

Here's a spreadsheet that I need to make for the sales of a sto

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