SUMPRODUCT result incorrect versus SUMIFS

[MarkHH]

Hi,
Having problem trying to figure out why sumproduct is returning different, and incorrect results, compared to SUMIFS.

Result sheet
Order Number Cost Centre Desc SUMIFS SUMPRODUCT
1026939 Dispensing 150 150
1026939 Dispensing 150 150
1026939 Dispensing 150 0
1026939 Dispensing 150 0
1026939 Dispensing 150 0
1026939 Dispensing 150 0
1026939 Steroid Dispensing 0 150
1026939 Steroid Dispensing 0 150
1026939 Steroid Dispensing 0 0
1026939 Steroid Dispensing 0 0
1026939 Steroid Dispensing 0 0
1026939 Steroid Dispensing 0 0

Formulae
SUMIFS(zcapacity!$G$2:$G$1784,zcapacity!$A$2:$A$1784,microstrategy!A4,zcapacity!$E$2:$E$1784,microstrategy!E4)

SUMPRODUCT((zcapacity!$A$2:$A$1784=microstrategy!A4)*(microstrategy!$E$2:$E$1784=TRIM(zcapacity!E4))*(zcapacity!$G$2:$G$1784*1))

150 is correct as two rows for the Order have dispensing matches ( 60+90).

The first problem is that SUMPRODUCT reporting zeros twice when sumifs correctly reports six times for the six rows of data have matches on columns E.

The second problem is that SUMPRODUCT makes incorrect matches on "Steroid Dispensing" which doesnot exist in zcapacity worksheet and appears to match on the "Dispensing" records. Almost like it is doing a "text contained" match.

I reformatted the column E data in both sheets to Text and ensured there where no leading/trailing spaces, but the error is not fixed. I have done an cell equals check between the Order number (col A) and Short Desc (col E) cells in both sheets and all show TRUE.

Extract of the zcapacity lookup data (sensitive columns hidden):

Col A Col B Col E
Order Short desc. Actual
1026939 COMPRESSION CONTAINMENT SUITE 2 (D4) 630
1026939 COMPRESSION CONTAINMENT SUITE 2 (D4)
1026939 CONTAINMENT SUITE 2 1,320
1026939 CONTAINMENT SUITE 2
1026939 DISPENSING 0
1026939 DISPENSING 0
1026939 DISPENSING 0
1026939 DISPENSING 60
1026939 DISPENSING 90
1026939 DISPENSING
1026939 DISPENSING CONTAINMENT SUITE
1026939 DISPENSING CONTAINMENT SUITE 60
1026939 DISPENSING CONTAINMENT SUITE

One oddity in the zcapacity data is that Col E results can be null or zero.

Very puzzling/disturbing to me, especially as I tend to prefer using sumproduct and it is certainly not trustworthy in this instance while sumifs is accurate.

Any ideas on what's going wrong?

Cheers,
Mark

 

[MarkHH]

Will try to edit down the data to a sample file and upload later.

 

[Hui]

A Sample file will be great

One formula has a Trim() function and so could that be an issue?
Another issue can be numbers which are actually text?

 

[MarkHH]

A Sample file will be great

One formula has a Trim() function and so could that be an issue?
Another issue can be numbers which are actually text?

Hi Hui,
I put in the trim function to make sure the missed matches weren't due to any spaces etc in the target cell causing a miss. Got same result with and without it.

Probably should have mentioned both datasets are downloads. The zcapacity is a excel formatted export from SAP, the other an export from a 3rd part reporting tool Microstrategy. Up to this occurence I have not have problems with either playing nicely.
Should have a file ready soon.

File attached. Have changed Order number in both instances to numeric, with no change in outcome. However I did notice that SUMIFS was able to report accurately even when I had one order column as text and the other as numeric, whereas all sumproduct formula failed to make any matches. So looks like SUMIFS is more robust/tolerant of data formats differences.

 

[Haz]

The formula is incorrect.

SUMPRODUCT((zcapacity!$A$2:$A$1784=microstrategy!A4)*(microstrategy!$E$2:$E$1784=TRIM(zcapacity!E4))*(zcapacity!$G$2:$G$1784*1))

SUMPRODUCT((zcapacity!$A$2:$A$1784=microstrategy!A4)*(zcapacity!$B$2:$B$1784=microstrategy!B4)*zcapacity!$C$2:$C$1784)

 

[MarkHH]

The formula is incorrect.

SUMPRODUCT((zcapacity!$A$2:$A$1784=microstrategy!A4)*(microstrategy!$E$2:$E$1784=TRIM(zcapacity!E4))*(zcapacity!$G$2:$G$1784*1))

SUMPRODUCT((zcapacity!$A$2:$A$1784=microstrategy!A4)*(zcapacity!$B$2:$B$1784=microstrategy!B4)*zcapacity!$C$2:$C$1784)

Hi haz,

My bad on that - the upload file has some columns removed to simplify the situation. In both sheets the order numbers are in col A, descriptions in col B, actual qty in col C.

The formulae now read as:

=SUMIFS(zcapacity!$C$2:$C$1784,zcapacity!$A$2:$A$1784,microstrategy!A4,zcapacity!$B$2:$B$1784,microstrategy!B4)

=SUMPRODUCT((zcapacity!$A$2:$A$1784=microstrategy!A4)*(microstrategy!$B$2:$B$1784=TRIM(zcapacity!B4))*(zcapacity!$C$2:$C$1784*1))

And the errors on sumproduct remain.

Did a evaluate formula on the Steroid Dispensing match in cell K10 and it shows the match is picking up the Dispensing actuals (60, 90).



Cheers,
Mark

 

[Haz]

The error remain because the SUMPRODUCT formula is wrong.

This part microstrategy!$B$2:$B$1784=TRIM(zcapacity!B4) is wrong, the worksheets are switched.

On the SUMIFS formula you compare zcapacity!$B$2:$B$1784 to microstrategy!B4, but on SUMPRODUCT you're comparing zcapacity!B4 to microstrategy!$B$2:$B$1784

 

[MarkHH]

The error remain because the SUMPRODUCT formula is wrong.

This part microstrategy!$B$2:$B$1784=TRIM(zcapacity!B4) is wrong, the worksheets are switched.

On the SUMIFS formula you compare zcapacity!$B$2:$B$1784 to microstrategy!B4, but on SUMPRODUCT you're comparing zcapacity!B4 to microstrategy!$B$2:$B$1784

Thanks Hui, you're right - just shows what fresh eyes can see!
I did your fix and then got another error - it was multiplying the correct result by 6 (the number of matches) - and the root cause

=SUMPRODUCT((zcapacity!$A$2:$A$1784=microstrategy!A4)*(zcapacity!$B$2:$B$1784=microstrategy!B4))*(zcapacity!$C$2:$C$1784)

I had bracketed the first two operations which multiplied the actual found ...

Once fixed the two formulae outputs are now identical ...

So human error after all. Mea culpa.
Time for another coffee.

Mark