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.

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 Tools>Addins>select
Analysis Toolpack.
In D2 enter = A2 but Format>Cells>Number>Custom 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.
Insert>Name>Define Name Data Refers to
=OFFSET($A$1,0,0,COUNT($A$A),4)

Now create the Pivot Table
Data>Pivot Table>Next>Range = Data>Next>Layout
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 OK>Finish
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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>