sum index results matching multiple criteria

[oats]

Hi,

I have a spreadsheet schedule that pulls quantities from another spreadsheet's cells. if the part number of the reference row matches the schedule cell's part number (row), and the date of the reference cell row is 2 workdays after that of the schedule cell's column, the quantity is put in the right place in the schedule. that part works. the problem I have is that sometimes there is more than one quantity with the same part number and date (due to the parts belonging to different jobs). when this is the case, only one of the quantities is picked up by the schedule. i want the schedule cells to sum the quantities of the reference file with matching criteria. i also don't want to have to change the reference file at all in order to do this. below is the function that works (besides summing the duplicates). the part after the " " is what i would need to alter.

HXS is the reference file where D is the part number column, I is the date column, and 5 is the index column referencing the quantity.

C is the part number column in the schedule
K is the date row in the schedule

=IF(ISNA(INDEX(HXS.xls!$D$16:$I$310,MATCH(1,IF(HXS.xls!$D$16:$D$310=$C5,IF(HXS.xls!$I$16:$I$310=WORKDAY(K$2,2),1,IF(HXS.xls!$I$16:$I$310=(K$2)+3,1)))),5))," ",(INDEX(HXS.xls!$D$16:$I$310,MATCH(1,IF(HXS.xls!$D$16:$D$310=$C5,IF(HXS.xls!$I$16:$I$310=WORKDAY(K$2,2),1,IF(HXS.xls!$I$16:$I$310=(K$2)+3,1)))),5)))

I know there's a lot of info here so let me know if it's confusing. Thanks for the help!

[NBVC]

Does this work?

=SUMPRODUCT(--(HXS.xls!$D$16:$D$310=$C5),--(HXS.xls!$I$16:$I$310=WORKDAY(K$2,2)),--(HXS.xls!$I$16:$I$310=(K$2)+3),HXS.xls!$H$16:$H$310)

this will return 0, if sum is 0 or none are found... which you can format or hide zeros for.

[Bob Phillips]

I haven't tested this, but here is a punt

=IF(ISNA(INDEX(HXS.xls!$D$16:$I$310,MATCH(1,IF(HXS.xls!$D$16:$D$310=$C5,IF(HXS.xls!$I$16:$I$310=WORKDAY(K$2,2),1,IF(HXS.xls!$I$16:$I$310=(K$2)+3,1)))),5))," ",
SUMPRODUCT((HXS.xls!$D$16:$D$310=$C5)*((HXS.xls!$I$16:$I$310=WORKDAY(K$2,2))+(HXS.xls!$I$16:$I$310=(K$2)+3)),HXS.xls!$H$16:$H$310))

[oats]

ok maybe i spoke too soon. while the sumproduct did work on the cells with duplicate matches, it doubled all the other quantities that didn't have duplicates. i'm thinking there needs to be an if statement of some sort in the sumproduct

[Bob Phillips]

ok maybe i spoke too soon. while the sumproduct did work on the cells with duplicate matches, it doubled all the other quantities that didn't have duplicates. i'm thinking there needs to be an if statement of some sort in the sumproduct

Try mine,

=SUMPRODUCT((HXS.xls!$D$16:$D$310=$C5)*((HXS.xls!$I$16:$I$310=WORKDAY(K$2,2))+(HXS.xls!$I$16:$I$310=K$2+3)),HXS.xls!$H$16:$H$310)

there is a subtle difference.


Watch the spaces that get inserted

[NBVC]

Or:

=SUMPRODUCT(--(HXS.xls!$D$16:$D$310=$C5),((HXS.xls!$I$16:$I$310=WORKDAY(K$2,2))+(HXS.xls!$I$16:$I$310=(K$2)+3)),HXS.xls!$H$16:$H$310)

The + between the 2 arguments for I16:I310 conditions acts as an OR() statement.

[Bob Phillips]

Or:

=SUMPRODUCT(--(HXS.xls!$D$16:$D$310=$C5),((HXS.xls!$I$16:$I$310=WORKDAY(K$2,2))+(HXS.xls!$I$16:$I$310=(K$2)+3)),HXS.xls!$H$16:$H$310)

The + between the 2 arguments for I16:I310 conditions acts as an OR() statement.

Isn't that just what I suggested, and had done so from the start?

[oats]

ok, the sumproduct does pick up duplicates now, but there's a glitch. say the reference file has a friday as the date. that means that the schedule's cells for both tuesday and wednesday will meet the criteria of either 2 workdays or 3 regular days ahead of the reference date (thus putting the quantity in both of the cells instead of just wednesday). i need something that will insert the quantity if it's 2 workdays ahead, but only insert the quantity that's 3 regular days ahead if that regular day happens to be on a weekend.


=SUMPRODUCT((HXS.xls!$D$16:$D$310=$C5)*((HXS.xls!$I$16:$I$310=WORKDAY(CM$2,2))+(HXS.xls!$I$16:$I$310=K$2+3)),HXS.xls!$H$16:$H$310)

[NBVC]

Probably better if you post a sample workbook showing what you want.

What if the date is Monday? Workday() function would return Wednesday's date and Date+3 would return Thursday?...

Not sure I fully understand.

[daddylonglegs]

Try like this

=SUMPRODUCT((HXS.xls!$D$16:$D$310=$C5)*((HXS.xls!$I$16:$I$310=WORKDAY(CM$2,2))+(HXS.xls!$I$16:$I$310 =K$2+3)*(WEEKDAY(K$2+3,2)>5)),HXS.xls!$H$16:$H$310)

[oats]

Thank you for suggesting the weekday function, i hadn't thought of that! the formula you gave me resulted in 0's for all the cells though. however, after playing with it a little, i finally found a solution (below). thanks for all the help guys!



=IF(OR(WEEKDAY(K$2)=4,WEEKDAY(K$2)=5),(SUMPRODUCT((HXS.xls!$D$16:$D$330=$C5)*((HXS.xls!$I$16:$I$330=WORKDAY(K$2,2))+(HXS.xls!$I$16:$I$330=K$2+3)),HXS.xls!$H$16:$H$330)),(SUMPRODUCT((HXS.xls!$D$16:$D$330=$C5)*(HXS.xls!$I$16:$I$330=WORKDAY(K$2,2)),HXS.xls!$H$16:$H$330)))