MATCH 3 COLUMS RETURN 4TH

[Guest]

Hi everyone. I have a formula that is stumping me. I hope someone can help.
I'm looking for a formula which will look at three columns, find exact
matches, and then add the totals of column H in the rows that match.
Essentially finding the number of boxes with the same dimensions in a
shipment.

Column H ... Column N Column O Column P
(pieces) (length) (width) (height)
row 1 5 14 14 9
row 2 5 7 7 12
row 3 10 14 14 9
row 4 6 12 12 24
etc.

The number of rows will vary but there will never be any blanks within the
data to be used.
The ideal formula would tell me there were 15 @ 14 x 14 x 9, 5 @ 7 x 7 x 12,
6 @ 12 x 12 x 24.

it's the getting columns n,o, and p to be seen as one value that is
confusing me.

thanks in advance!!!

mike

 

[Guest]

Sounds like a data filter would be easier perhaps (Data menu > Autofilter
command, then select the parameter from the value list); You could also do
this with an array formula, where you compare all of N to O to P, then sum H
where your NOP criteria are met; it'd look something like
=SUM((N1:N100=14)*(O1:O100=14)*(P1100=7)*H1:H100))
and press control+shift+enter to make the array

 

[Jim Cone]

Mike,
You could use Subtotals on the Data menu to achieve something
similar to that shown below.
Insert a column to the right of the pieces column and enter a formula
like this... =N6&"x" &O6&"x"&P6 and fill down.
Add a title at the top of the column... "size".
Sort on that new column and then insert a blank column to the
right of it.
Use subtotals on the pieces and size columns asking it to sum on the pieces column.

(pieces) Size
5 14x14x9
10 14x14x9
15 14x14x9 Total
6 12x12x24
6 12x12x24 Total
5 7x7x12
5 7x7x12 Total
26 Grand Total
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"mike" <[email protected]>
wrote in message
Hi everyone. I have a formula that is stumping me. I hope someone can help.
I'm looking for a formula which will look at three columns, find exact
matches, and then add the totals of column H in the rows that match.
Essentially finding the number of boxes with the same dimensions in a
shipment.

Column H ... Column N Column O Column P
(pieces) (length) (width) (height)
row 1 5 14 14 9
row 2 5 7 7 12
row 3 10 14 14 9
row 4 6 12 12 24
etc.
The number of rows will vary but there will never be any blanks within the
data to be used.
The ideal formula would tell me there were 15 @ 14 x 14 x 9, 5 @ 7 x 7 x 12,
6 @ 12 x 12 x 24.
it's the getting columns n,o, and p to be seen as one value that is confusing me.
thanks in advance!!!
mike

 

[Dallman Ross]

Mike,
You could use Subtotals on the Data menu to achieve something
similar to that shown below.
[snipped here]

I don't know why, but the original article from "Mike" is missing
in my thread; I only see this and one other follow-up. So I'll use
Jim Cone's quoting of the original, which is that to which I wish
to respond):

If Column Q shall contain the grand totals, then in Q1, then
dragging down:

=SUMPRODUCT(--(N$1:N$100=N1),--(O$1:O$100=O1),--(P$1$100=P1),H$1:H$100)

Adjust the "100" to fit at least the number of rows you actually have
(more is also okay), noting that the ranges should all be equivalent
in length.

Dallman Ross

 

[Guest]

Dallman, Jim.

Thank you so much for your help with this. Using Jim's theory i got it
working, but now using Dallman's formula I should be able to get it even more
automated.

Thanks again to everyone for sharing your knowledge on this forum. It has
saved me countless hours and helped me learn a lot more about excel.

mike
Ps- sorry about the double post. MS gave me an error msg the first time.

Mike,
You could use Subtotals on the Data menu to achieve something
similar to that shown below.
[snipped here]

I don't know why, but the original article from "Mike" is missing
in my thread; I only see this and one other follow-up. So I'll use
Jim Cone's quoting of the original, which is that to which I wish
to respond):

If Column Q shall contain the grand totals, then in Q1, then
dragging down:

=SUMPRODUCT(--(N$1:N$100=N1),--(O$1:O$100=O1),--(P$1$100=P1),H$1:H$100)

Adjust the "100" to fit at least the number of rows you actually have
(more is also okay), noting that the ranges should all be equivalent
in length.

Dallman Ross

 

[Guest]

the final formula which worked for my needs. Just in case it can help anyone
else.

col h=pieces, col n=length, col o=width, col p=height.
formula displays: # of pieces @ L x W x H.

=IF(ISNUMBER(H3),SUMPRODUCT(--(N$3:N$31=N3),--(O$3:O$31=O3),--(P$3$31=P3),H$3:H$31)&" @ "&N3&" x "&O3&" x "&P3,"")

Dallman, Jim.

Thank you so much for your help with this. Using Jim's theory i got it
working, but now using Dallman's formula I should be able to get it even more
automated.

Thanks again to everyone for sharing your knowledge on this forum. It has
saved me countless hours and helped me learn a lot more about excel.

mike
Ps- sorry about the double post. MS gave me an error msg the first time.

Mike,
You could use Subtotals on the Data menu to achieve something
similar to that shown below.
[snipped here]

I don't know why, but the original article from "Mike" is missing
in my thread; I only see this and one other follow-up. So I'll use
Jim Cone's quoting of the original, which is that to which I wish
to respond):

I'm looking for a formula which will look at three columns, find
exact matches, and then add the totals of column H in the rows
that match.

Column H ... Column N Column O Column P
(pieces) (length) (width) (height)
row 1 5 14 14 9
row 2 5 7 7 12
row 3 10 14 14 9
row 4 6 12 12 24
etc.

The number of rows will vary but there will never be any blanks
within the data to be used. The ideal formula would tell me
there were 15 @ 14 x 14 x 9, 5 @ 7 x 7 x 12, 6 @ 12 x 12 x 24.

If Column Q shall contain the grand totals, then in Q1, then
dragging down:

=SUMPRODUCT(--(N$1:N$100=N1),--(O$1:O$100=O1),--(P$1$100=P1),H$1:H$100)

Adjust the "100" to fit at least the number of rows you actually have
(more is also okay), noting that the ranges should all be equivalent
in length.

Dallman Ross

 

[Dallman Ross]

the final formula which worked for my needs. Just in case it can
help anyone else.

Glad my input could be of help.

Btw, are you perhaps superseding your prior articles when you
follow up? Your prior articles keep disappearing from this thread
for me on my server. I was going to follow up to your response of
a couple of hours ago, and now that I came back to do so, this is
here (which quotes that earlier article), but the earlier article
is not. If you are superseding, or canceling, I kind of wish you
wouldn't, as having the earlier articles in the thread around for a
bit can be useful.

Dallman Ross