Excel: Data validation and SUMPRODUCT 1

[Humlan]

Trying to replace MS Project with a simpler Excel project management planning and reporting tool, but I'm stumbling when trying to calculate how many hours that was calculated and is reported for each project per week.

I've set up three namned lists ("Projects", "People" and "TimeType") in a sheet called "Definitions." These are then used to populate drop-down menus in a sheet called "Time."
The three first columns use these drop-downs so that all rows in col A can have the project names, all rows in col B can be populated with names of people, and all rows in col C can be either "Planned" or "Worked" time. The rest of the columns are a daily calendar with one day per column.
The idea is that I can plan how much time each person should work on a project on one row ("TimeType" is "Planned"), and then add a new row with the "TimeType" set to "Worked" to report daily on how much work was actually performed.

In the third sheet called "ReportPerProjectAndWeek" I can choose from the same "Project" and "TimeType" popup menus. I'd like to calculate the sum of all time planned/reported per project each week (i.e. all rows and the seven columns of the week matching the criteria) in the sheet "Time".

Can't figure out how calculate the weekly sum per project and time type. I've tried this:

=SUMPRODUCT(--(Time!$A$4:$A$400=$A$4);--(Time!$C$4:$C$400=$C$4);Time!G4:M500)

...but it didn't cut it. Is there a quick way to fix this, and also to spread out this formula so that all week columns in the "ReportPerProjectAndWeek" sheet get the correct date range from the "Time" sheet?

I've uploaded the Excel file here:
<

http://www.brinna.se/ProjectReportTest.zip>

Thankful for all insights!

/Jonas Hummelstrand

 

[KenWright]

First part of question:-

In cell D4 put the following and copy across and down:-

=SUMPRODUCT((Time!$A$4:$A$400=$A4)*(Time!$C$4:$C$400=$C4)*Time!$G$4:$M$400)

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Second part:-

In Row 1 where you have 14, 15, 16 etc in the first cell of each weekly block:-

Put something in A1, dont care what, just a 1 will do, as you can delete it straight after anyway:-

Now select the whole of Row 1 and do Edit / Go To / Special / Blanks. Now type = and then hit the LEFT arrow once and then hit CTRL+ENTER at the same time.

You should now see the week numbers in each cell in the whole range. You can now delete A1:F1 as it is no longer needed.

Now over on your summary sheet 'ReportPerProjectAndWeek', in cell D4 use the following formula and then just copy across and down and it will automatically adjust for each row /column

=SUMPRODUCT((Time!$A$4:$A$400=$A4)*(Time!$C$4:$C$400=$C4)*(Time!$G$4:$IV$400)*(Time!$G$1:$IV$1=--RIGHT(D$3,2)))

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Forgot to mention:-

In your initial formula:-

=SUMPRODUCT(--(Time!$A$4:$A$400=$A$4);--(Time!$C$4:$C$400=$C$4);Time!G4:M500)

It was fundamentally flawed in that the ranges differed in size, ie you used A$:A400, C4:C400, BUT G4:M500. No can do.

Also, the format you used with double unary coercion (ie the -- bit), doesn't seem to work when calculating a 2 dimensional range of numbers (ie the amended G4:M400 bit), I had to use multiplication as opposed to double unary.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Humlan]

Thanks for the quick reply, I'll give it a try right away!

Trying to be a broadcast animator while hacking Excel at the same time...

 

[Humlan]

First part of question:-
In cell D4 put the following and copy across and down:-
=SUMPRODUCT((Time!$A$4:$A$400=$A4)*(Time!$C$4:$C$400=$C4)*Time!$G$4:$M$400)

You do mean in the sheet "ReportPerProjectAndWeek", I presume? It works great there, but your next post also has a formula to put into the same cell, so I'm a bit confused.

Second part:-

In Row 1 where you have 14, 15, 16 etc in the first cell of each weekly block:-

Put something in A1, dont care what, just a 1 will do, as you can delete it straight after anyway:-

Now select the whole of Row 1 and do Edit / Go To / Special / Blanks. Now type = and then hit the LEFT arrow once and then hit CTRL+ENTER at the same time.
You should now see the week numbers in each cell in the whole range.

Didn't quite work, it only filled row 1 of the sheet "Time" with ones. If you wanted me to fill the week row (row 1) of the sheet "Time" with the week number in each cell, I've done this in another manner, so D4:IV4 now look like this:
14 14 14 14 14 14 14 15 15 15 15 15 15 15
Is this what you were after?

Now over on your summary sheet 'ReportPerProjectAndWeek', in cell D4 use the following formula and then just copy across and down and it will automatically adjust for each row /column

=SUMPRODUCT((Time!$A$4:$A$400=$A4)*(Time!$C$4:$C$400=$C4)*(Time!$G$4:$IV$400)*(Time!$G$1:$IV$1=--RIGHT(D$3,2)))

This is where I'm stumped, since I already pasted the first formula into the D4 cell in the sheet "ReportPerProjectAndWeek."

If I replace the first formula with the latest one, I get an error. (I have to replace the PRODUCTSUM and the RIGHT commands with the localized versions since I'm stuck with a Swedish Office package at work, but I don't think this is causing the problem.)

Thanks for all your kind help!

 

[KenWright]

Sorry, ignore the first formula, it simply answered your first question. The formulas after that supersede the first.

The reason you got ones may be because your formula was locked on A1, eg $A$1. Other than that I have no idea why it did that, but yes, 14,14,14,14,14,15,15,15 etc was what i was after.

With that done, the file seems to work fine for me.

In your posted file, update it to put your email addy in and i will fix it my end as I described and send it to you and then when you open it in your version it should translate automatically

Am off out in a few hours for the evening, so am here for probably 2 1/2 hours more tonight.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Humlan]

RIGHT(D$3,2)))

I did a bit of research trying to understand what you did, and I found that the syntax for RIGHT used a semicolon. Then everything worked, thanks a million!

Strangely, it seems my installation (Office 2002) requires semicolons to delimit statements in many commands, even though the helpfile consistently writes commands with commas...

From the helpfile: Syntax: IF(logical_test,value_if_true,value_if_false)
All I get is formula errors if I don't use semicolons.

 

[KenWright]

That's OK, I think it's something to do with Regional Settings or something like that, but that's why I suggested I do it and you open it, as Excel will do all the translation for you.

Anyway, glad you got sorted

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------