Find Quantities in Excel sheet 2

[TIgerV]

I have a sheet that I don't control that comes to me with: (| Separates cells for here.)
A B. C. D.
Line|Part Num|Description|Qty|Price
1. |12345 |Widget | 4. | 1.00
2. |12346 |Gadget | 5. | 1.00
3. |12345. |Widget | 6. | 3.00
4. |12347. |DooHickey | 1. |12.00

As you can see, Widgets show up on different lines.

I want to build a sheet that will find all the widgets, total the quantity and let me know how many to order.
Same for Gadgets and Same for doohickeys. Some items appear once, some appear 8 places. Again, I DONT CONTROL THAT SHEET.

I would think my sheet would do a vlookup, but how to I addd the 4 widgets from C1 to the 6 widgets from C3?


--TIger

 

[Deniall]

The formula
=SUMPRODUCT(--(B1:B4="Widget"),C1:C4)
should give you one way to get started.
There will be others.

 

[combo]

It's not clear for me, from the description headers are in A1:E1, lines 1-4 are in column A, the rest of data in col. B. If so, I would do 'text to columns' for B2:B5 to have the the same type of data in columns, next summarize A1:E5 with pivot table.

combo

 

[Andrzejek]

Plus, if that's your data:

[pre]
A B. C. D.
Line|Part Num|Description|Qty |Price
1. |12345 |Widget | 4. | 1.00
2. |12346 |Gadget | 5. | 1.00
3. |12345. |Widget | 6. | 3.00
4. |12347. |DooHickey | 1. |12.00[/pre]

Your Qty. (column C) have a period, so I suspect it is Text, not a Number.
If that's the case, calculations are very hard using Text :-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Livia8]

You could use the SUMIFS function, but you'd need to have a separate list of "Description".

Otherwise you could create a pivot table.

 

[SkipVought]

Hi,

"I have a sheet that I don't control that comes to me…"

Are you sure it's a "sheet," meaning a spreadsheet? Chances are its some kind of a text file (.txt, .csv, .dat), that has the pipe delimiters. You may be able to OPEN the file with Excel, but I would recommend otherwise. I, rather, would open a new workbook and IMPORT text data into a worksheet using Data > Get External Data > IMPORT Text File..., where you can specify the data type of each column, if you select DELIMITED and supply the [highlight #FCAF3E]correct delimiter[/highlight][highlight #FCAF3E][/highlight] ([highlight #FCAF3E]|[/highlight]).

In your case the column types should be identified as…
[Pre]
Line: General
Part Num: Text
Qty: General
Price: General
[/pre]

The reason I recommend using Data > Get External Data > IMPORT Text File... is Faq68-7375.

Your data doesn't indicate that there would be a problem, but it is a good practice.

From there, a Pivot Table summary report, as combo suggested, would give you all the information that you seem to need.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!