Sumproduct

Can't seem to get this to return correct results. Viewed a few other threads and thought the advice would help but didn't so as a novice excel person, I thought I'd ask for help from the pros. Basically I need to sum the values from one column if the criteria in two other columns are met. Here is what I've tried before.

=SUMPRODUCT(('Detail page 1'!B1:B65456="UPS GROUND")*('Detail page 1'!Q1:Q65456="P")*('Detail page 1'!P2:P65456))

=SUMPRODUCT(--('Detail page 1'!B1:B65456="UPS GROUND"),--('Detail page 1'!Q1:Q65456="P"),--('Detail page 1'!P2:P65456))

=SUMPRODUCT(--('Detail page 1'!B1:B65456="UPS GROUND"),--('Detail page 1'!Q1:Q65456="P"),'Detail page 1'!P2:P65456)

You don't really state what the exact problem is though the final range is a different size to the previous ranges.

Given this fact the first formula will return #N/A whereas the latter two will return #VALUE! given use of double unary.

If you have headers in row 1 then commence ranges from row 2 or use the 3rd approach but change the final range such that it commences from row 1.

Whenever you use SUMPRODUCT keep ranges to a minimum - it's a very inefficient formula - if you have that much data you should concatenate B and Q into a single string and use a SUMIF approach.

If you use the above method then you can include the header row without issue... but double unary approach means each array must identical in terms of dimensions

If you use the multiplication method (which does not nec. have this requirement) then it follows given the explicit coercion taking place your summation range should not include non-numerics.

Basic example:

=SUMPRODUCT(--({TRUE,FALSE,TRUE}),--({TRUE,TRUE,TRUE}),{"a",1,2}) -> 2

the "a" in the summation range will be ignored in similar vein to a SUMIF ignoring non-numerics in sum_range (no explicit coercion taking place)

Conversely

=SUMPRODUCT({TRUE,FALSE,TRUE}*{TRUE,TRUE,TRUE}*{"a",1,2}) -> #VALUE!

the "a" here causes an issue as it's explicitly coerced given * - hence 1*"a" -> #VALUE!

For more info. on SUMPRODUCT see Bob Phillips' white paper: http://www.xldynamic.com/source/xld.SUMPRODUCT.html