sumproduct

  • Thread starter Thread starter matt
  • Start date Start date
M

matt

I am still having trouble getting this formula to work - I
keeping getting #VALUE

Please take a look and let me know if there is something I
am doing wrong
I am trying to pull values from a spreadsheet based on
three criteria (A6, A7, D5)

=SUMPRODUCT(('Sorted Data'!$B$1:$B$75=$A$6)*('Sorted Data'!
$C$1:$C$75=$A$7)*('Sorted Data'!$Q$1:$Q$75=D$5)*'Sorted
Data'!E1:E75)

thanks
m
 
without knowing exactly what you mean by "pull data"
(i.e. count, sum, etc.) here's a suggestion:

{=SUM(if('Sorted Data'!$B$1:$B$75=$A$6,1,0)*if('Sorted
Data'!$C$1:$C$75=$A$7,1,0)*if('Sorted Data'!
$Q$1:$Q$75=D$5,1,0)*'Sorted Data'!E1:E75)}


that will evaluate the 3 comparison ranges (b, c, & q)
and, if all 3 conditions are met, will add the
corresponding value in e to an overall sum. if you
haven't worked with array functions before, you can type
in the formula without the braces, and hit ctrl + enter
and excel will add them to signify it is an array
function.

hope this helps.

mike
 
By any chance does any cell in 'Sorted Data'!E1:E75 have text?

By using the multiplication operator in SUMPRODUCT(), the arrays are
multiplied first (with text returning a #VALUE! error), before
they're passed on to SUMPRODUCT(). If you use the "canonical"
comma-form of SUMPRODUCT(),

=SUMPRODUCT(--('Sorted Data'!$B$1:$B$75=$A$6), --('Sorted
Data'!$C$1:$C$75=$A$7), --('Sorted Data'!$Q$1:$Q$75=$D$5), 'Sorted
Data'!$E$1:$E$75)

text is treated as zero, instead. It's also about 20% faster than
using *s.





form.In article <[email protected]>,
 
Back
Top