Sumproduct finetuning HELP

[St3ff3ns]

Hi guys,

I am currently trying to retrieve a Month To Date data based on 2 tables (one is actuals, the other one is FCST) ,

The objective is to sum up what is in the past (actuals) with Forecast to reach a sort of projection of Full month figure,

Therefore i added extra lines (rows B1:F15) to give more details about the week numbers, how many percentage of the week is contained in the concerned month, a Y/N line to determine if the values from that table have to be included or not

The basics of my formula are to sumproduct the values in cellss AC3:AC18 based on different criterias:
- Cust names
- Month

and by multiplying them also by their prorata weight in the given month,

What I would need is to include in this formula the Y/N criteria from cells C7:G7 in order to just select the data needed in comparison with the current week to reach a more or less logical MTD figure

Could you also have a look at what I did wrong please ?


Thanks in advance !

Steffen

[AliGW]

Steffen - I can't access your file right now, but based on other formulae you've asked us to troubleshoot, please check that you have changed the ranges to match. What error message are getting?

[CAntosh]

Try the formula below in AC3 (and fill down) to see if I'm understanding what you're expecting:

=IF(AB$3="","",SUMPRODUCT(--($C$11:$G$11=$AA$1),($C$14:$G$14),OFFSET($C$16:$G$16,MATCH($AB3,$B$17:$B$32,0),0))+SUMPRODUCT(--($C$12:$G$12=$AA$1),($C$15:$G$15),OFFSET($C$16:$G$16,MATCH($AB3,$B$17:$B$32,0),0)))

If I'm on the right track, please clarify what you expect from Y/N - do you want to only include "Y" columns in the calculation?


EDIT:
To include "Y" weeks only, try:

=IF(AB$3="","",SUMPRODUCT(--($C$11:$G$11=$AA$1),--($C$7:$G$7="Y"),($C$14:$G$14),OFFSET($C$16:$G$16,MATCH($AB3,$B$17:$B$32,0),0))+SUMPRODUCT(--($C$12:$G$12=$AA$1),--($C$7:$G$7="Y"),($C$15:$G$15),OFFSET($C$16:$G$16,MATCH($AB3,$B$17:$B$32,0),0)))

[loginjmor]

Hi -

I looked at your SUMPRODUCT formula and there are a number of problems with it. The formula in AC3 looks like this:

=IF(AB$1="","",SUMPRODUCT(($C$11:$G$11=$AA1)*$C$14:$G$14*INDEX($C$17:$H$40,MATCH(AB$3,$B$54:$B$361,0),))+SUMPRODUCT(($C$12:$G$12=$AA1)*$C$15:$G$15*INDEX($C$17:$H$40,MATCH(AB$3,$B$17:$B$361,0),)))

First, all of the arrays within a SUMPRODUCT must be the same size. the INDEX function you have will only return a single value. So SUMPRODUCT won't like that.

Second, the first range, from C11 to G11 is an array with 5 values. However, looking at your data, you only have four columns. That's a problem.

Third, you have two SUMPRODUCTS: the first one checks the beginning date against AA1 (which is the end of August) which is fine, but the second one also checks the end of period dates against this same cell AA1. The problem being, that half of the formula will always resolve to zero because the date you are checking against is August and the last cell in F12 is September - so that's not a match, AND all of the percentages in C15-E15 are zero. In short, the second half of the equation does nothing. I'm not sure what you are trying to do with this part of the formula so I can't offer a guess.

But, we can clear up the first two problems. Replace the formula in Cell AC3 with the following:

=IF(AB3="","",SUMPRODUCT(($C$11:$F$11=$AA$1)*($C$14:$F$14*OFFSET($B$17,MATCH(AB3,$B$17:$B$40,0)-1,1,1,4))))

First, this changes the lengths of the arrays to 4 columns (C,D,E,F). Second, I think you are trying to multiply the array of values that match the row label. We will use OFFSET to return the array and the MATCH function to tell offset what row to start with. This is only the first half of your equation. If you can explain what the second half is supposed to be doing, we can add that part to it (the second SUMPRODUCT).

Hope this helps.

[St3ff3ns]

Hi, The formula is working perfectly in the attached file, however, when going to my original file, it is only giving me 0 values,
Do you think that the fact of having pivot tables instead of values copy pasted as in te attached file , is influencing the result ?

Thanks in advance for your feedback and support,

Steffen

[AliGW]

The only way we can be sure is by seeing your original file. I think that it's a range issue, as it has been twice before in files of yours that I've looked at in the recent past. I'll ask again: have you double-checked that you have adapted the ranges properly for your original file's layout?

[St3ff3ns]

Hi Ali,

I have found the issue, it was the format of the date the formula was looking for, not corresponding with the format of the selected cell which was representing the date,

However I was wondering how could I include an extra criteria within the below formula:
=IF(AB3="";"";SUMPRODUCT(($C$11:$F$11=$AA$1)*($C$14:$F$14*OFFSET($B$17;MATCH(AB3;$B$17:$B$40;0)-1;1;1;4))))

I would like to add a criteria definying the sum of values containing only "Y" between cells C7:F7,

This formula is becoming a bit too complicated for me and i still need to learn...

Thanks in advance,

Steffen

[AliGW]

Have you tried this? Not tested!

=IF(AB3="";"";SUMPRODUCT(($C$7:$F$7="Y")*($C$11:$F$11=$AA$1)*($C$14:$F$14*OFFSET($B$17;MATCH(AB3;$B$17:$B$40;0)-1;1;1;4))))

[St3ff3ns]

It it working !! Solved ! thanks again !!

[AliGW]

Great! Thanks for letting me know. I'm learning a lot about these SUMPRODUCT formulae from your queries!!!