sumproduct a whole column

S

steve

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
 
M

Mike H

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

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

Mike
 
B

Bob Phillips

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)
 
S

steve

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
 
P

Peo Sjoblom

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
 
S

steve

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
 
B

Bob Phillips

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)
 
S

steve

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)
 
D

driller

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
 
S

steve

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
 
Top