Conditional sumproduct?

  • Thread starter Thread starter rahirah
  • Start date Start date
R

rahirah

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
 
Back
Top