SUM IF Question 1

[TanmedIT]

I need to add up the "Qty" when the "P/N", "L/N", and "From"

Here is my data:

P/N L/N Qty From To
sta-d1 k09714 1 N-FG STA-K21
STA-D1 K09714 20 N-FG STA-K21
STA-D3 K08949 3 N-FG STA-K21
STA-D2 K09720 5 N-FG STA-K21
MTP-PS2L 2 N-CNSBLK MT00092
MTP-PS2R 2 N-CNSBLK MT00092
MTP-PS3L 2 N-CNSBLK MT00092
MTP-PS3R 2 N-CNSBLK MT00092
MTP-RS2R 2 N-CNSBLK MT00092
MTP-RS2L 2 N-CNSBLK MT00092
MTP-RS3R 2 N-CNSBLK MT00092
MTP-RS3L 2 N-CNSBLK MT00092
MTP-2712 5 N-CNSBLK MT00092
MTP-2714 5 N-CNSBLK MT00092
MTP-2716 5 N-CNSBLK MT00092
MTP-2718 5 N-CNSBLK MT00092
MTP-2720 5 N-CNSBLK MT00092
MTP-2724 5 N-CNSBLK MT00092
MTP-2728 5 N-CNSBLK MT00092
MTP-3212 5 N-CNSBLK MT00092
MTP-3214 5 N-CNSBLK MT00092
MTP-3216 5 N-CNSBLK MT00092
MTP-3218 5 N-CNSBLK MT00092
MTP-3220 5 N-CNSBLK MT00092
MTP-PS2L 2 N-CNSBLK MT00093
MTP-PS2R 2 N-CNSBLK MT00093
MTP-PS3L 2 N-CNSBLK MT00093
MTP-PS3R 2 N-CNSBLK MT00093
MTP-RS2R 2 N-CNSBLK MT00093
MTP-RS2L 2 N-CNSBLK MT00093
MTP-RS3R 2 N-CNSBLK MT00093
MTP-RS3L 2 N-CNSBLK MT00093
MTP-2712 5 N-CNSBLK MT00093
MTP-2714 5 N-CNSBLK MT00093
MTP-2716 5 N-CNSBLK MT00093
MTP-2718 5 N-CNSBLK MT00093
MTP-2720 5 N-CNSBLK MT00093
MTP-2724 5 N-CNSBLK MT00093
MTP-2728 5 N-CNSBLK MT00093
MTP-3212 5 N-CNSBLK MT00093
MTP-3214 5 N-CNSBLK MT00093
MTP-3216 5 N-CNSBLK MT00093
MTP-3218 5 N-CNSBLK MT00093
MTP-3220 5 N-CNSBLK MT00093
MTP-PS2L 2 N-CNSBLK MT00100
MTP-PS2R 2 N-CNSBLK MT00100
MTP-PS3L 2 N-CNSBLK MT00100
MTP-PS3R 2 N-CNSBLK MT00100
MTP-RS2R 2 N-CNSBLK MT00100
MTP-RS2L 2 N-CNSBLK MT00100
MTP-RS3R 2 N-CNSBLK MT00100
MTP-RS3L 2 N-CNSBLK MT00100
MTP-2712 5 N-CNSBLK MT00100
MTP-2714 5 N-CNSBLK MT00100
MTP-2716 5 N-CNSBLK MT00100
MTP-2718 5 N-CNSBLK MT00100
MTP-2720 5 N-CNSBLK MT00100
MTP-2724 5 N-CNSBLK MT00100
MTP-2728 5 N-CNSBLK MT00100
MTP-3212 5 N-CNSBLK MT00100
MTP-3214 5 N-CNSBLK MT00100
MTP-3216 5 N-CNSBLK MT00100
MTP-3218 5 N-CNSBLK MT00100
MTP-3220 5 N-CNSBLK MT00100

 

[TanmedIT]

Sorry I meant to say:

I need to add up the "Qty" when the "P/N", "L/N", and "From" are equal to others in the same column.

For instance Rows 2 and 3 match so the total Qty would be 21.

Thanks in Advance

 

[SkipVought]

Hi,

How about using a PivotTable and SUM of Qty

Skip,
[sub]
[red][/red]
[/sub]

 

[TanmedIT]

Sorry, that can't work because I am using this worksheet to created a 2nd sheet with formatted text. It has to be a formula that I will use in a cell

 

[SkipVought]

I don't understand why it HAS TO BE a formula?

Skip,
[sub]
[red][/red]
[/sub]

 

[TanmedIT]

Because I am using the original information to calculate values on a separate worksheet, which is ALREADY formatted..

I can probably figure it out, but I was hoping to save sometime by asking here.

I know it can be easily done in a pivot table, but I don't want to recreate the wheel on a project that I have put numerous hours into already

 

[AnotherHiggins]

I'm not clear on what you want to report.

What is on the other sheet? Will you have a list of each possible "P/N", "L/N" and "From" combinations? Then a sum of "Qty" beside each?

Without more information about you goals, and given that you are looking for an alternative to the best and easiest solution (a pivot table), all I can suggest is that you look into SumProduct.

Example:[COLOR=blue white]
=SumProduct((A2:A65 = "STA-D1") * (B2:B65 = "k09714") * (D265 = "N-FG") * (C2:C65))[/color]
would give you 21.

With respect, it seems to me that you already are re-inviting the wheel. And the wheel's name is Pivot Table.

If what I've described isn't what you're after, please provide more details about what output you are expecting.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

What happens here between lines 4 & 5 with L/N????
[tt]
P/N L/N Qty From To
STA-D2 K09720 5 N-FG STA-K21
MTP-PS2L 2 N-CNSBLK MT00092
[/tt]

Skip,
[sub]
[red][/red]
[/sub]

 

[TanmedIT]

John,

that is exactly what I needed. Thanks. And I truly apologize for coming off sounding like a jerk. I know this could have been a much better report with Pivot Tables, but this is what I had to work with and it had to be done by 4pm today (its 3:11)

Thanks again for your replies