Solved: Sum of unique value using sumproduct

[CCkfm2000]

Hi all,

Please see attached file.

I?m trying to compare some data from 2 different sources.

Both sets of data are imported into different sheets ?SAP? & ?WMS?

In ?checker? sheet columns D to H is a pivot table getting the data from ?WMS?.

I?ve got most of the formulas working in ?checker? sheet using sumproduct in columns I to J getting the data from ?SAP?

As you can see I?ve got some data missing in columns I to J which I?ve highlighted.

For life of me I can?t workout why it?s missing the data off.

Any help will be much appreciated.

Thanks again.

[Bob Phillips]

It looks to me to be simply missing data, nothing matches the criteria for those rows.

[CCkfm2000]

thanks xld, found that I did not refresh the pivot table.

can anyone suggest a better way formula in column A & B to check column D?

thanks again

[Bob Phillips]

A6: =IF(E6="","",INDEX($D$6:$D6,MAX(IF($D$6:$D6<>"",ROW($D$6:$D6)-ROW($D$6)+1))))

an array formula

B6: = A6

[CCkfm2000]

A6: =IF(E6="","",INDEX($D$6:$D6,MAX(IF($D$6:$D6<>"",ROW($D$6:$D6)-ROW($D$6)+1))))

an array formula

B6: = A6

i've tried this and it copies the 1st cell down leaving blanks. sorry must be somthing i'm doing wrong

[Bob Phillips]

Did you array enter it?

[CCkfm2000]

got it thanks xld....