Need help creating a simple (?) SUM formula!

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!
0
Utf
1/7/2010 1:57:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
621 Views

Similar Articles

[PageSpeed] 21

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 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!
0
Utf
1/7/2010 2:16:01 AM
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 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!
0
Utf
1/7/2010 5:21:01 AM
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 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!
0
Utf
1/7/2010 8:26:04 AM
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 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!
0
Utf
1/8/2010 1:26:02 PM
Reply:

Similar Artilces:

How to create a ledger sheet
Hello. I have a database with the following tables:Customers, Orders, and PaymentsThe Customers table has a one-to-many relationship with the Orderstable. The Customers table also has a one-to-many relationship withthe Payments table.I am trying to create what I think is commonly called a ledger sheet.This sheet simply shows the dollar amount of every order that anindividual customer has placed (debits) and every payment that thecustomer has made (credits). It also shows a running balance. Entriesare shown in chronilogical order.I am having trouble creating a report that would draw debits from...

web address help
whats the web address for outlook so i can sign on? I have it stored on my home computer but dont have it here thanks Outlook doesn't have a web address unless you use an Exchange server. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com <anonymous@discussions.microsoft.com> wrote in message news:64da01c3e61b$58282fc0$a401280a@phx.gbl... > whats the web address for outlook...

Notes or comments in formula bar
Hello, I vaguely recall an article I read that mentioned I could insert a note or comment into the formula bar. Can someone tell me how to do that? I'm not talking about Insert-> Comment. Example: If I were to have A1+B1, there's a trick that will allow me to add a note within the formula bar such as "this formula adds apples and oranges." Thanks =A1+B1+N("Type Note Here") >-----Original Message----- >Hello, > >I vaguely recall an article I read that mentioned I could >insert a note or comment into the formula bar. Can >someone...

HELP!! Viewing email images
I have just recently switched to using Outlook rather than Outlook Express as my email program. I now find that any image attachments (.jpg, .bmp etc.) I receive are not visible onscreen as they used to be in Outlook Express. I have to click on each attachment icon to view... It's annoying, especially if I get a series of pics...I have to manually ope each one... Does anyone know how to set Outlook so that it shows me these images onscreen??? Many Thanks, Darren. ...

I've got a formula for stones and pounds
Hello, I've got a column that displays a series of weights in stones & pounds (both in the one cell). How do I get a chart to recognise and display in the same format? Regards, mikelenno What is the format of the cells with stone & pounds It is best to enter such values a 10 3/14 and format the cells with # ??/14 best wishes from one who is INT(275,14) stone & MOD(250,14) lbs -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "mikelenno" <mikelenno@discussions.microsoft.com> wrote in message news:5AD608FA-5367-4CED-AD76-1F54228EFDBA...

help installing exchange 2000 in one of 3 sites
I have a domain cotoso.com installed and configured as AD for 3 companies interlinked together. and + a total of 3 DC in 3 different sites that are connected by VPN. i have exchange installed in each site connected on the same domain contoso.Local(+ E-mail -->contoso.com) + hosting their own companie domain name such as x.com or y.co or z.com for each company. I have a routing link configured + 3 administrative groups in exchange system manger for each company. one DC in one site that also has exchange 2000 installed on it has to be changed because of poor performance. now i'm ...

Need help getting this code to compile under VS.NET 2003 (compiles under VS6.0).
I'm tasked with converting a LARGE project from VS6.0 to VS.NET 2003. Here is a VERY scaled down version of an error I'm getting. I must be getting rusty, but the way to get around this compiler error is just not coming to me. If you need to compile this, just create a Win32 console app and specify to add support for MFC, then copy this code into the cpp file and press compile. class CLogItem : public CObject { protected: virtual BOOL CheckConflict(CLogItem* pItem) const; }; struct MapEntry; //forward declaration.. typedef CArray<MapEntry, MapEntry&> CMapEntryArray;...

help!! smtp protocol error occurred
exchange 2000 srv/windows 2k srv smtp protocol error occurred when trying to send mails. receives fine. Still waiting for some relavent information so we can help you solve your problem. >-----Original Message----- >exchange 2000 srv/windows 2k srv >smtp protocol error occurred when trying to send mails. >receives fine. > > >. > I had that problem, I telneted into the servers that the error occurred and found I was on a blacklist. Had to sumit to an open relay test, and bam, no more problems. ...

How do I create a bookfold document in Word 2007?
I am new to Word 2007. How do I create a document in booklet form? Thanks I'm sure you'll get better answers in an MSWord forum. dadolim wrote: > > I am new to Word 2007. How do I create a document in booklet form? Thanks -- Dave Peterson ...

Help !!!!
I was migration my Exchange 2000 to 2003 I do my forest prep and domain prep But i can't create mailboxes ??? Why??? some documentation please... What actually happens? What error do you get? -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "A.dian�" <Adian@discussions.microsoft.com> wrote in message news:31CB593F-F4DD-4CAB-9F4E-C2131BF7CF95@microsoft.com... > I was migration my Exchange 2000 to 2003 I do my forest prep and domain prep > But i can't create mailboxes ??? > Why??? > some documentation...

To write living will do I need special format
I just need to change and update a living trust You should consult someone with the appropriate legal knowledge in the jurisdiction in which you are domiciled. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "belladonna" <belladonna@discussions.microsoft.com> wrote in message news:F25A64CB-355F-44E9-A005-16AA61DA15A7@microsoft.com... > I just need to change and update a living trust ...

object not being created
hello, I created a dialog based application.I pop up a modal dialog from the menu created in my application.Class midialog is associated with this dialog resource.By mistake i created an obj of this class in the menu handler as shown in code below. OnMenu()//handler in my application dialog class.i.e.main dialog { midialog m_d; m_d.DoModal();//creating my modal dialog } after couple of daysi realized that i wanted the obj not inside the handler but in the class CMainDlg(class of the main dialog).So i removed above code and tried to declare a variable of type midialog m_d(again,s...

IF / SUM
I have a spreadsheet with 17 rows, each representing a project name. In the columns, I have 2 cells for each week: Est and Actual. At the end of each row, I would like a row total for Est and another for Actual. As there are 20+ weeks represented, I cannot select the cells that have the criteria I'm looking for without using a more complex formula than SUM. Any ideas? The spreadsheet looks like this: Project Name Apr 3-9 Apr 10-16 Apr 17-23 Total Est | Actual Est | Actual Est | Actual E | A Project A 20 | 22 40 | 37 25 | 26 85...

Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...

form and query problem. please help.
All tables are linked with weak entities. However, when i enter data on the form I can't get it to let me enter more than one partipicant without access generating a new invoice id. however i need one invoice to many participants. It wont work and i have no idea what to do at this point. in addition the workshop will not let me add workshop to invoice. this is a small mdb and i'd like to email it to anyone who can assist me with the relationships as I think this is the problem but I don't know what to do. please help me. INVOICE invoiceNO - autonumber invoice prices WORKSHOP wo...

suggestion needed !!!!!!!!!!
In my company the want to calculate phone calls done by each sales person at the end of the day. Please give me suggestion how to implement this. Mahain, A quick solution would be 1) Create an advance find view based on the criteria you are looking for ie. Activity of the type phone call, Status = completed 2) Export the list to Excel (Dynamic pivot table/worksheet) 3) If pivot : Drag and drop relavent fields 3) If worksheet: filter by date and use sum. The advantage of dynamic excel report is it updates itself when ever you open the sheet. Hope this is helpful Venkat http://venkatbendalam...

Simple worksheet becomes large and slow
I use 2 simple worksheets daily. They contain little data and should be less than 50k. I change them by copying data from various other spreadsheets into them, then saving or saving as with a new name. Some of these spreadsheets for some reason become very large (1.5mb or more) and moving from cell to cell becomes very slow. Copying from those spreadsheets to new sheets can take up to 30 seconds for a single cell to copy. There is nothing complex in any of them, just text, some multiplication and sum. The sheets eventually become so slow that the only way I can work with them is b...

Integration Manager need a registration key for test company?
Hi all, Can anyone tell if I need the registration key for IM if I am doing some testings on Test company (World Online)? Also, can I specify the company database in IM for the data to import to? Please advise. Thanks You do not need a reg key for the Two Company, and if you are using the standard adapter the data will integrate into the Company that is open in Great Plains. You can Specify which database you want to integrate into by using the SQL Optimizer. You do not need great Plains open for this adapter. "Terry" wrote: > Hi all, > > Can anyone tell if I nee...

Summing distinct rows in same cell
Sorry for the bad title I'm not sure hte best way to sum up this issue. Fruit Price Total Apples 0.69 40 Bananas 0.34 38 Lemons 0.55 15 Oranges 0.25 25 Apples 0.5 10 Pears 0.59 40 Almonds 2.8 10 Cashews 3.55 16 Peanuts 1.25 20 Walnuts 1.75 12 Apples 0.5 5 Given the data above I'm trying to sum the totals for only the Appl rows. So basically I need to search the Fruit column find the row that have "Apples" and then sum their corresponding Totals, giving m 55. I've been playing with this for hours with Lookups and Indexes bu am not really getting anywhere. Also, I can...

Newbe help with a lookup
Hi, I&#8217;m trying to make some modifications to a salary matrix and I&#8217;m having trouble with a lookup. In column A I have a series of numbers from 0 to 30 representing pay grade steps where 0 represents a new employee and 30 represents an employee having completed 30 years of service. In column B I have an annual salary for each of the corresponding pay grade steps. I want to take a new salary and find the closest salary in column B and return the corresponding step number. For example if the new salary is $73,415.13 and the matrix shows step 15 is 73,205 and step 16 is 73...

Formula for ageing debt
I'm trying to do a spreadsheet so that when I put a debt amount in the first column, after 10 days it will automatically move to the next column and move on every 10 days till a 60 days column where it gets added to any amount already there. Can anyone clue me up to a formula to do this? Thanks -- Barnie Gumble Esq You will have to devise a way to associate the date of initial entr with the entry itself, then it is relatively simple to do - could yo have a lookup table on a separate sheet that returned entry dates fo all amounts, BUT the amounts would have to be unique, ie n duplica...

formula to get sheet name
hi, can a formula retreive a worksheet name or does it need to be macro based? thanks, N.S. Hi Nigel, This formula that takes care of the possible situation whereby you have only one sheet in the workbook and its name is the same as the workbook's. Note: CELL("filename") will only work if the file has been saved at least once and if the file is opened in a different language system, the argument "filename" will need to be changed manually to the corresponding word (e.g. in Spanish "nombrearchivo"): =LOOKUP(REPT("z",255),SUBSTITUTE(MID(CE...

HELP
We have an application that is used by over 8000 people worldwide. One of our users is just starting to have the following problem: Run-time error '-2147467259(80004005)': Method 'Add' of object 'CommandBarControls' failed I have read that this problem can be caused by trying to open the file in Internet Explorer. The user has told me that she has tried opening MS Excel and then the .xls file and she still gets the same problem when the file is trying to open. She is the only one that has had this problem. Could there be something wrong with her Excel settings or som...

Conditional sum
Hi Can you help? I would like to sum a matrix according to a condition in a row and in different condition in a column. I know how to do this one dimensionally (either in the row or the column) using sumproduct, but what about two dimensions? Let me give an example, number of widgets produced each week by each type of machine A B C D 1 weeks>>1 2 3 2 Type 1 10 15 11 3 Type 2 20 5 10 4 Type 1 5 12 21 Etc So, I’d like the formula to be a...

Why won't formulas obey vertical alignment commands?
I have a spreadsheet with numbers and text. Many numbers are derived from cell formulas. I can vertically align the text in all the cells in the numbers/text worksheet, but if I change the numbers to display formulas only, then the cell values automatically align at the bottom of the cell no matter what I have set the alignment to be. I just want to align the formulas at the tops of the cells so that I have room to write underneath them on a printed copy. Is this possible? Why not increase the Row height and write *above* them? -- HTH, RD =============================================...