sumproduct / array forumlas / Overall percentage complete

[hawkint]

Hi

I am trying to use an excel sheet to write a report. This report will use data from several other excel sheets / sources.

I need to report total invoices and percentage complete against several activities for several periods. See attached excel sheet.

- The pink data is a schedule of 'periods' with a start and end date. This data is created once at the start of our projects.
- Orange lists the activities and their respective budgets. This is created once at the start of our projects.
- Blue lists the incoming invoices, the invoice date and the applicable activity.
- Green is a report from our managers. This report is generated every period and gives an indication of the % complete of the activity.
- The grey area is the report I'm trying to write.

The 3 yellow boxes are the ones I am struggling with. Whilst I can manually calculate the % complete for that period which is equal to the sum of (invoices/total budget) * activity % complete for each period (as I have done on the attached sheet) this is not really acceptable or scaleable. I need this report to carry out the calculation automatically.

Can anyone shed any light - i'd be very grateful?

Many thanks
Tom.

[hawkint]

Ok. Half way there.

I have added a helper column which makes it better but not perfect.

[daddylonglegs]

Without your helper column you can use this formula in G24 copied down

=SUMPRODUCT((G$5:G$16=E24)+0,SUMIF(A$19:A$22,F$5:F$16,B$19:B$22),H$5:H$16)/B$25

[TMS]

I've moved your tables around a little so that I can use some dynamic named ranges. It should mean that it is scaleable without any intervention once it is set up.

The formula is a bit, well, long and kind of repetitive, but it seems to work. It's a little more readable with the named ranges.

Please see the aupdated workbook and let me know if this helps.

Regards

[TMS]

Looking at the other solutions, it seems I went the long way round the houses to reach a solution.

Still, what else am I going to do on a rainy Saturday afternoon in Manchester?

Regards

[hawkint]

Without your helper column you can use this formula in G24 copied down

=SUMPRODUCT((G$5:G$16=E24)+0,SUMIF(A$19:A$22,F$5:F$16,B$19:B$22),H$5:H$16)/B$25

Thanks

This works great - only problem is that I don't understand it... Any chance you could walk me though it?....

[daddylonglegs]

It's not really too different from what you are doing with your helper column. Your formula using the helper column was

=SUMPRODUCT((G5:G16=E24)*(I5:I16))/B25

Now in I5 you had

=SUMPRODUCT(($A$19:$A$22=F5)*($B$19:$B$22))*H5

Which would be better as a SUMIF, i.e.

=SUMIF($A$19:$A$22,F5,$B$19:$B$22)*H5

Now the advantage of a SUMIF like that is that you can expand it for a range of values, so if you expand the F5 and H5 references to the whole column you get

=SUMIF($A$19:$A$22,F$5:F$16,$B$19:$B$22)*H$5:H$16

which you can then put in SUMPRODUCT in place of I5:I16, i.e.

=SUMPRODUCT((G5:G16=E24)*(SUMIF($A$19:$A$22,F$5:F$16,$B$19:$B$22)*H$5:H$16))/B25

which is the same as the formula I suggested......give or take the odd syntax tweak.....

It would also be possible to use LOOKUP in place of SUMIF, which might be more transparent....but for LOOKUP to work A19:A22 needs to be sorted ascending (whci is what you have anyway), i.e.

=SUMPRODUCT((G$5:G$16=E24)+0,LOOKUP(F$5:F$16,A$19:B$22),H$5:H$16)/B$25

[hawkint]

Thanks - it was the use of SUMIF I didn't understand - now I do.

Thanks.
Tom.