sumproduct a whole column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

here is my function:

=sumproduct((C:C="Epoxy") * (D:D="Straight") * (E:E))

as you've already guessed, I get #Value!. I don't know up front how many
rows to include, so I included the entire column. What can I do to my
formula to ignore blanks?

Or do I need to set up some sort of dynamic list?

Thanks,
Steve
 
If your prepared to take a chance on the last row try:-

=SUMPRODUCT((C1:C65535="Epoxy") * (D1:D65535="Straight") * (E1:E65535))

Mike
 
Create a dynamic range

Insert>Name>Define...

with a name of rngProduct and a RefersTo value of

=OFFSET($C$1,0,0,COUNTA($C:$C),1)

and another of rngType with a RefersTo value of

=OFFSET($D$1,0,0,COUNTA($C:$C),1)

and another of rngAmount with a RefersTo value of

=OFFSET($E$1,0,0,COUNTA($C:$C),1)


and use

=SUMPRODUCT(rngProduct="Epoxy")*(rngType="Straight")*rngAmount)



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob,

What if some rows are partially filled in? Couldn't this lead to different
size arrays, which would cause the sumproduct() function to blow up?

I think this is a problem with my design.

I built the same spreadsheet using pivot tables, but that required some VBA
to make it work properly. I was challenging myself to do it without VBA.

Thanks,
Steve
 
Not because of blanks, it's because of text, that means if you have formulas
that return "" then it will fail. It can be rewritten as

=SUMPRODUCT(--(C1:C65535="Epoxy"),--(D1:D65535="Straight"),E1:E65535)

are you seriously using 65535 rows, if so expect the workbook to be very
slow


--
Regards,

Peo Sjoblom
 
Peo,

Thank you, that works. Can you please explain the --? I have no idea how
this syntax works. And no, I am not using all those rows.

thanks,
Steve
 
No because I have based the ranges all on the same column count, column C
here. But why would you have partially filled rows?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob,

Ah, that makes sense now.

I would have partially filled rows as the user fills in the information
manually. This isn't necessarily a bad thing, because it will force them to
fill everything in.

I like seeing multiple solutions to this. Can you explain what's happening
in the function that Peo posted:
=SUMPRODUCT(--(C1:C65535="Epoxy"),--(D1:D65535="Straight"),E1:E65535)
 
good day steve,

i've seen nice solutions already, maybe its time to check which one works
faster and easy for you...*so we can learn also...

maybe this is less than 2 cents,for the last row u wish..maybe something
like this...

=SUMPRODUCT(--(C1:C65535="Epoxy"),--(D1:D65535="Straight"),E1:E65535)
+
((C65536="Epoxy")*(D65536="Straight")*(E65536)*(and(C65536<>"",D65536<>"")))

check for ()...
it may look long yet it can stand in a while for once as a simple formula.


--
regards,
driller

*****
- dive with Jonathan Seagull
 
driller,

I don't need that many rows. 1,000 is more than sufficient. here's what I
found worked best for me, but I'm still not sure of the exact meaning of --.

=SUMPRODUCT(--($B$2:$B$1000=$J$2), --($C$2:$C$1000=K$3),
--($E$2:$E$1000=$J4), ($H$2:$H$1000))

I am referencing the headings of my tables to search for the text in the
data. This worked out nice, because I was able to make multiple tables, sort
of like pivot tables.

Thank you everybody for your help!
Steve
 
Back
Top