sumproduct a whole column
 

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

sumproduct a whole column
 

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

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

Mike

"steve" wrote:

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

sumproduct a whole column
 

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

sumproduct a whole column
 

Mike,

I would still have an issue with blank cells. I get #Value!

Thanks,
Steve

"Mike H" wrote:

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

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

Mike

"steve" wrote:

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

sumproduct a whole column
 

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

sumproduct a whole column
 

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





"steve" wrote in message
...
Mike,

I would still have an issue with blank cells. I get #Value!

Thanks,
Steve

"Mike H" wrote:

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

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

Mike

"steve" wrote:

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

sumproduct a whole column
 

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

"Peo Sjoblom" wrote:

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





"steve" wrote in message
...
Mike,

I would still have an issue with blank cells. I get #Value!

Thanks,
Steve

"Mike H" wrote:

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

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

Mike

"steve" wrote:

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

sumproduct a whole column
 

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

sumproduct a whole column
 

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

sumproduct a whole column
 

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)*(an d(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



"steve" wrote:

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

"Peo Sjoblom" wrote:

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





"steve" wrote in message
...
Mike,

I would still have an issue with blank cells. I get #Value!

Thanks,
Steve

"Mike H" wrote:

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

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

Mike

"steve" wrote:

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

sumproduct a whole column
 

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

"driller" wrote:

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)*(an d(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



"steve" wrote:

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

"Peo Sjoblom" wrote:

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





"steve" wrote in message
...
Mike,

I would still have an issue with blank cells. I get #Value!

Thanks,
Steve

"Mike H" wrote:

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

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

Mike

"steve" wrote:

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

sumproduct a whole column
 

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob

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

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