Sumproduct formula, add up by date, average by date

Hi All,

I need to tweak the attached 2016-2017 totals sheet to work out the 'total labour hours' and GC/H total' on a month by month basis. The date the formula needs to reference is Column C in current orders. ****However, I need the GC/H totals to be an average for the month, like when you select cells and excel works out the average bottom right. So for the entries in blue (current orders) so far for Feb it would be an average of £193.73****

So in essence similar to column G (2016-2017 totals sheet) where it has the formula

Please Login or Register  to view this content.

I don't understand how it knows what columns to look at on the current order sheet.

I have highlighted what I want added up on the current order sheet in blue, we have only just started filling this in so next financial year it will be filled in from the start, so will need the formula to reflect this (like the one above).

I hope this makes sense and thank you very much for your help.

Sam

Thanks John.

The file you have uploaded works, but when I drag them down as per the email they come up with errors. Do you know why this is not working? I have attached the issue.

Many thannks,

Sam

Your file you sent had #DIV0 error in P2 and a spurious blank in column O which "removed" by setting O to zero except for your data (in blue).

These cause the errors: sorry I did not mention these in my post- use file I posted as it is corrected.

Originally Posted by JohnTopley

Your file you sent had #DIV0 error in P2 and a spurious blank in column O which "removed" by setting O to zero except for your data (in blue).

These cause the errors: sorry I did not mention these in my post- use file I posted as it is corrected.

Thanks John, but I need to paste these formulas back into the original sheet as I removed some sensitive data (so can't use your one)

Is there anything that can be added to the formula that can remove the value and div/0 errors?

Thanks

Sam

For the #DIV0 you can add IFERROR around formula (already present in the Blue section - you did these?)
=IFERROR(your formula,"")

Or

=IFERROR(your formula,0)

For VALUE errors you could select range down to your new data (O2:O1310) and then "Clear Contents" if there is no data present (as per your posted file)

Thanks for all your help John. It worked a treat!

Thank you for the feedback and rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hi all,

I have changed this back to unsolved, as I need to change the formula in L8 (totals tab). Currently the formula gives an average of the values in column Q in the current order tab (associated to that particular month).

Please can someone tweak the formula so that it gives an average based on the GC (column P) against labour total hours (column O). So far for March it would be £10264/58 = £176.97. It has to ignore rows 1333 and 1334 as there are no hours associated to it.

I hope this makes sense and thanks in advance for the help.

Regards,

Sam

In L8

=IFERROR(SUMPRODUCT(('Current Orders'!$P$2:$P$2002)*('Current Orders'!$O$2:$O$2002>0)*('Current Orders'!$C$2:$C$2002>=$D8)*('Current Orders'!$C$2:$C$2002<=EOMONTH($D8,0)))/SUMPRODUCT(('Current Orders'!$O$2:$O$2002)*('Current Orders'!$C$2:$C$2002>=$D8)*('Current Orders'!$C$2:$C$2002<=EOMONTH($D8,0))),"")

Hi John, Thanks ever so much. It works! All the best

Sam