If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional sumproduct?
I'm trying to create a spreadsheet that A) enters the number of projects worked on per day for several different kinds of projects, B) multiplies that number by a point value which differs for each different project, and C) sums the resulting values for the day. The goal is to be able to see daily totals over the course of a month. In the first cell I only want the sum of projects done on Monday, Week 1, and in the cell below I only want the sum of projects done on Monday, Week 2, and so forth. The next column would be totals for Tuesday, Week 1, then Tuesday, Week 2, etc. I've been doing this by using SUM on all the individual multiplications necessary, but as we add more project types, it's getting to the point where there are too many arguments. I've tried using sumproduct, but I can't figure out how to make it conditional--i.e. right now it's summing everything entered under the Monday column, regardless of what week it is. I'm sure there must be a way to do this, perhaps by using the IF function with SUMPRODUCT, but I can't figure out how to make them work together. Any suggestions would be much appreciated. -- rahirah ------------------------------------------------------------------------ rahirah's Profile: http://www.excelforum.com/member.php...o&userid=30380 View this thread: http://www.excelforum.com/showthread...hreadid=500410 |
#2
|
|||
|
|||
Conditional sumproduct?
Hi
You could solve this with Sumproduct, but I would be more inclined to use a Pivot Table. Assuming your data is set out as Date, Project in columns A and B with row 1 being headers. First, add 2 extra columns C and D headed Week and Day In C2 enter =Weeknum(A2) Weeknum is part of the Analysis Toolpack addin, so if it comes up with #NAME for you, go to ToolsAddinsselect Analysis Toolpack. In D2 enter = A2 but FormatCellsNumberCustom ddd and it will display the day of the week. As your data will be growing, then it would be best to create a dynamic range. InsertNameDefine Name Data Refers to =OFFSET($A$1,0,0,COUNT($A$A),4) Now create the Pivot Table DataPivot TableNextRange = DataNextLayout Drag Day to the Column area, Drag Week to the Row Area, Drag Project to the Page Area Drag Project again to the Data Area and ensure that it is Count of Project (double click the field to change if it is not Count) Click OKFinish You will now have a summary of all of your data, use the dropdown on Project to give the analysed data for just that project. For more information on setting up and amending Pivot tables take a look at http://www.contextures.com/xlPivot02.html -- Regards Roger Govier "rahirah" wrote in message ... I'm trying to create a spreadsheet that A) enters the number of projects worked on per day for several different kinds of projects, B) multiplies that number by a point value which differs for each different project, and C) sums the resulting values for the day. The goal is to be able to see daily totals over the course of a month. In the first cell I only want the sum of projects done on Monday, Week 1, and in the cell below I only want the sum of projects done on Monday, Week 2, and so forth. The next column would be totals for Tuesday, Week 1, then Tuesday, Week 2, etc. I've been doing this by using SUM on all the individual multiplications necessary, but as we add more project types, it's getting to the point where there are too many arguments. I've tried using sumproduct, but I can't figure out how to make it conditional--i.e. right now it's summing everything entered under the Monday column, regardless of what week it is. I'm sure there must be a way to do this, perhaps by using the IF function with SUMPRODUCT, but I can't figure out how to make them work together. Any suggestions would be much appreciated. -- rahirah ------------------------------------------------------------------------ rahirah's Profile: http://www.excelforum.com/member.php...o&userid=30380 View this thread: http://www.excelforum.com/showthread...hreadid=500410 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional Format Not Working | KMH | General Discussion | 0 | December 22nd, 2005 05:32 PM |
Conditional formula - language needed | gamebird | Worksheet Functions | 7 | October 10th, 2005 11:48 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | Worksheet Functions | 7 | July 1st, 2004 10:22 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | General Discussion | 7 | July 1st, 2004 10:22 PM |
Conditional Sumproduct | Marcotte A | Worksheet Functions | 2 | June 19th, 2004 08:42 AM |