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)


"Bob Phillips" wrote:

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)

"steve" wrote in message
...
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

"Bob Phillips" wrote:

Create a dynamic range

InsertNameDefine...

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)

"steve" wrote in message
...
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