Counting question

Hello,

Column 1 contains a product ID and could contain just one entry or several 
entries depending upon how many reels of that product ID we have in 
inventory.  Column 2 has the reel quantity in feet.  I need to get a total 
quantity in feet for each product ID.

Column 1   Column 2
Prod ID     Feet
ABC         100
ABC         150
ABC         200
XYZ         50
XYZ         50

I need the total product, in this example ABC = 450 ft and XYZ = 100 ft.  I 
have over 3000 lines and over 600 products I can't do it manually.  I think a 
pivot table would do it but I'm not sure...

tks,
steve
0
sjs (30)
12/1/2004 5:21:07 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
555 Views

Similar Articles

[PageSpeed] 55

Have a look in HELP index ofr SUMIF

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"sjs" <sjs@discussions.microsoft.com> wrote in message
news:4F99B36D-3A98-408E-996B-3AB5BD7BCD86@microsoft.com...
> Hello,
>
> Column 1 contains a product ID and could contain just one entry or several
> entries depending upon how many reels of that product ID we have in
> inventory.  Column 2 has the reel quantity in feet.  I need to get a total
> quantity in feet for each product ID.
>
> Column 1   Column 2
> Prod ID     Feet
> ABC         100
> ABC         150
> ABC         200
> XYZ         50
> XYZ         50
>
> I need the total product, in this example ABC = 450 ft and XYZ = 100 ft.
I
> have over 3000 lines and over 600 products I can't do it manually.  I
think a
> pivot table would do it but I'm not sure...
>
> tks,
> steve


0
Don
12/1/2004 5:41:02 PM
To use sumif, you have create 600 sumif forumulaes, which is tedious.

A quick one: Sort on column 1 and do a subtotal (choosing Data -> Subtotal 
from the menu bar). Collapse the detail and you have 600 lines subtotal lines 
to print.

"sjs" wrote:

> Hello,
> 
> Column 1 contains a product ID and could contain just one entry or several 
> entries depending upon how many reels of that product ID we have in 
> inventory.  Column 2 has the reel quantity in feet.  I need to get a total 
> quantity in feet for each product ID.
> 
> Column 1   Column 2
> Prod ID     Feet
> ABC         100
> ABC         150
> ABC         200
> XYZ         50
> XYZ         50
> 
> I need the total product, in this example ABC = 450 ft and XYZ = 100 ft.  I 
> have over 3000 lines and over 600 products I can't do it manually.  I think a 
> pivot table would do it but I'm not sure...
> 
> tks,
> steve
0
h317 (5)
12/1/2004 6:55:05 PM
sjs wrote:
> Hello,
> 
> Column 1 contains a product ID and could contain just one entry or several 
> entries depending upon how many reels of that product ID we have in 
> inventory.  Column 2 has the reel quantity in feet.  I need to get a total 
> quantity in feet for each product ID.
> 
> Column 1   Column 2
> Prod ID     Feet
> ABC         100
> ABC         150
> ABC         200
> XYZ         50
> XYZ         50
> 
> I need the total product, in this example ABC = 450 ft and XYZ = 100 ft.  I 
> have over 3000 lines and over 600 products I can't do it manually.  I think a 
> pivot table would do it but I'm not sure...
> 
> tks,
> steve
if the functions in the freely downloadable file at 
http://home.pacbell.net/beban are available to your workbook, then array 
enter into C2:C700

=ArrayUniques(IF(A2:A700<>"",A2:A700,""))

  and enter into D2:D700

=SUM(vlookups(C2,A$1:B$700,2)) and copy it down as far as needed

Alan Beban

0
unavailable (273)
12/1/2004 6:59:01 PM
On Wed, 1 Dec 2004 09:21:07 -0800, sjs <sjs@discussions.microsoft.com> wrote:

>Hello,
>
>Column 1 contains a product ID and could contain just one entry or several 
>entries depending upon how many reels of that product ID we have in 
>inventory.  Column 2 has the reel quantity in feet.  I need to get a total 
>quantity in feet for each product ID.
>
>Column 1   Column 2
>Prod ID     Feet
>ABC         100
>ABC         150
>ABC         200
>XYZ         50
>XYZ         50
>
>I need the total product, in this example ABC = 450 ft and XYZ = 100 ft.  I 
>have over 3000 lines and over 600 products I can't do it manually.  I think a 
>pivot table would do it but I'm not sure...
>
>tks,
>steve

A Pivot table should do fine.

Drag the Prod ID to Rows; and drag the Feet to Data.


--ron
0
ronrosenfeld (3122)
12/1/2004 7:40:00 PM
You could do a unique filter on Column 1 to summarise all the Product ID's 
into a single list

Data>Filter>Advanced Filter>Copy to Another Location>Unique Records Only

Then use a sumif function in the coloumn adjacent to the filtered list to 
determine how much is in each product ID

=SUMIF(A:A,D2,B:B)

"sjs" <sjs@discussions.microsoft.com> wrote in message 
news:4F99B36D-3A98-408E-996B-3AB5BD7BCD86@microsoft.com...
> Hello,
>
> Column 1 contains a product ID and could contain just one entry or several
> entries depending upon how many reels of that product ID we have in
> inventory.  Column 2 has the reel quantity in feet.  I need to get a total
> quantity in feet for each product ID.
>
> Column 1   Column 2
> Prod ID     Feet
> ABC         100
> ABC         150
> ABC         200
> XYZ         50
> XYZ         50
>
> I need the total product, in this example ABC = 450 ft and XYZ = 100 ft. 
> I
> have over 3000 lines and over 600 products I can't do it manually.  I 
> think a
> pivot table would do it but I'm not sure...
>
> tks,
> steve 


0
Eagle
12/2/2004 9:24:17 AM
Reply:

Similar Artilces:

How to incorporate page counts in TOC
I need to find out how to incorporate each document’s total page count into the Table of Contents instead of the current page number. In my case, we have 20-60 separate document files that need a single TOC that states the total number of pages for each file. Example: Section No. of Pages 100 3 200 2 300 10 I forgot to mention, I am using MS Word 2007 "Krystal" wrote: > I need to find out how to incorporate each document’s total page count into > the Table of Contents instead of the current page number. > > In my case, we have 20-60 s...

Total daily e-mail count?
This might seem like an odd question, but my CIO is asking for a total e-mail count for messages both sent and/or received by our organization on any given day. I'm currently using Exchange Server 2003. Can this information be found within System Manager, or will a third party utility be needed to accumulate such information? Thanks. Perfmon will give you some basic metrics. Otherwise have a look at MessageStats from http://quest.com/messagestats. Fully-functional 30-day eval on the site. -- David Sengupta M.T.S., B.Sc., MVP, MCSE, MCSE 2000, CCA Ottawa, Canada Exchange Rep...

Questions about rebuilding a db 03-28-10
Lateley I've notices my database freezing from time to time, so I tried the following... a) created a new data base b) imported all tables, queries, forms, ...... ,modules c) reset the References d) reset the Startup options e) compile Everything is working great now, but I have a few questions about this process. Did I miss any crucial steps? (don't want any surprises later) Is there a prefered order to do these steps? Finally, the database is now about 30% smaller, even though I was regularly compiling, compacting/repairing the former one. I'd like to know w...

microsoft publisher question
Is there any way to retrieve a document that wasn't saved? Doubtful. You might get lucky and Publisher created a temp file. In Windows Explorer, type %temp% in the address bar. Find any file that has a pub prefix. Change the .tmp to .pub and try opening it. -- Mary Sauer http://msauer.mvps.org/ "llcoby" <llcoby@discussions.microsoft.com> wrote in message news:F76C488D-E9CB-469C-AE5C-BC720692437A@microsoft.com... > Is there any way to retrieve a document that wasn't saved? ...

Installation of CRM on SBS 2003 Question
When installing CRM 3.0 on Microsoft SBS 2003 will installing into the default web site cause any problems with any thing else running from there such as Outook Web access, sharepoint, etc? Is it better to create a "new web site" when installing? Thanks, John Lammey You should create a new website. -- Matt Wittemann http://icu-mscrm.blogspot.com "John Lammey" wrote: > When installing CRM 3.0 on Microsoft SBS 2003 will installing into the > default web site cause any problems with any thing else running from there > such as Outook Web access, sharepoint,...

Excel question reposted
Hi I am reposting this problem with more detail in the hope that there is a workaround to this. Initially what I did was created a spreadsheet and then pasted 'screenshots' into the spreadsheet with comments bedside. I then shared the workbook, as I require other people's comments. Once they have entered their comments in the spreadsheet, I will then move the pasted screenshots and all comments to another 'Sheet'. The problem is that now that the spreadsheet is shared, I cant move the pictures. Thanks again Julia It's true that there are significant restricti...

Outlook 2010 question
I want to delete my existing pst folders and start over with a pst that I have saved to a safe location. How do I do that? You delete the present pst and put the pst files that you have backed up manually back in the same place from where you deleted the present pst -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. This posting is provided "AS IS" with no warranties, and confers no rights. http://www.microsoft.com/protect "RichCo" <richcolo@gmail.com> wrote in message news:4cd59...

Physical inventory question #3
I have a customer that is looking at an inventory last month and in the Summary box it shows what quantities and values were expected, however, when I click Print and export the data to CSV, the expected totals do not match what is in the summary box. Any ideas? Is there a way to subtotal the Active report when printed to screen? Thanks in advance. Phil Try the 2nd icon "select columns to group" on the top of the report. You can subtotal them by what column you choose to group them by. "Phil V" wrote: > I have a customer that is looking at an inventory last...

Exchange 5.5 IMC question
Hi, anyone know how to stop an Exchange 5.5 IMC from recieving but still allow it to send?? Cheers John On the Connections tab, set the Transfer Mode to Outbound Only. Restart the IMS. -- Denis McDowell [MSFT] "John" <John@4sol.com> wrote in message news:OnQV9krLEHA.2612@TK2MSFTNGP10.phx.gbl... > Hi, > > anyone know how to stop an Exchange 5.5 IMC from recieving but still allow > it to send?? > > Cheers > > John > > Great tip!! This is the answer to my problem (see the "Can I run NAV on received e-mails?"). I need to install NA...

User Form Security Question
Hi, I'm creating a form, which onces completed transfers the data over to the spreadsheet. But the guys in work don't really pay much attention to what they have entered , so i was wondering is there any way to make the user double check every entry? I was thinking, once the user clicks ok, a msgbox would appear giving the user the value they entered, click ok if correct or cancel to amend. Is there a easy way to do this? Cheers. -- harpscardiff ------------------------------------------------------------------------ harpscardiff's Profile: http://www.excelforum.com/mem...

Checkbox customization question
How can I implement checkbox in MSCRM ? I have a contacts with multiple professions. From a list of available professions as checkboxes, the user should be able to check all the applicable professions. Any ideas. Try Powertrak Core from www.axonom.com "Arch" <discussion11@yahoo.com> wrote in message news:b87a74db.0405210819.3232b5d8@posting.google.com... > How can I implement checkbox in MSCRM ? I have a contacts with > multiple professions. From a list of available professions as > checkboxes, the user should be able to check all the applicable > professions. Any...

address database question
I am setting up an address database in Excel 2003 that I need to be able to use to make labels for mailings. I have people that are in different "categories" and will want to group them by that category, depending on who I am sending the mailing to. For instance, I have a person, Person A, who is a general supporter, lives in the area, and sponsors a child. Person A is in three of the possible 5 categories. Another person, Person B, might be simply a general supporter (i.e., in one category). How can I best organize the data - and the headings - so that I can make labels ...

overtime charging question
A B C date hours worked 9 name hours offered 10 hours charged 3 Hi I have a sheet set up this way and want to chart overtime. Anything over 8 hours is time and a half, Saturday's are time and a half all day and Sunday is double charge.How can i set this up so it recognizes the date and charges correctly on the weekends. Will Excel recognize the date as weekend? The example above shows someone working 9 but offered 10 so charged 1 1/2 x 2 or 3 hours charged. Thanks Karl Karl Chip Pearson has a good sit...

eConnect question #3
When integrating inventory items via eConnect, is it possible to assign the item to all existing sites? Tony: Yes you can do that - by building your own code in the taUpdateCreateItemRcdPost procedure - I added the following code and now when I pass one item all of the sites of I have set up are now inserted into the IV00102 table. I just did a quick test and it seemed to work out fine. Here is the simple code you would add to the post procedure. I hope this is what you are looking for - I built this on 9.x (hopefully it pastes in here okay). Code to insert: insert into IV00102 (I...

Security Model Design Question
Poke a Hole in this Security Model! I think I have a pretty good security design, but wanted a second opinion :). The following Security model works – however, I have to lock the user out of Excel downloads and Mail merges so that the user can’t download the company’s entire contact list. Am I overlooking something that would allow users to own individual Accounts and Contact, yet preclude others from seeing opportunities attached to contacts they own? Background Assumptions 1 – Some sales reps are organized Geographically – others are organized Functionally (by industry). 2 – Sales...

Count records b/w time range
I have a sheet which has data with date & time fields like this 19/10/2005 10:10 19/10/2005 11:30 19/10/2005 12:12 19/10/2005 12:15 19/10/2005 10:12 How do I make a formula to pull out count between each half hour slot? like: 10:00 - 10:30 =2 10:30 - 11:00 =0 11:00 - 11:30 =1 11:30 - 12:00 =0 12:00 - 12:30 =2 Please help. Cheers! Sunny =sumproduct(--(B1:B100>--"10:00:00"),--(B1:B100<=--"10:30:00")) -- HTH RP (remove nothere from the email address if mailing direct) <sunilkeswani@gmail.com> wrote in message news:1131569006.191782.216510@g44g200...

Shared Borders Question....
Can 'Shared Borders' be .asp rather than .htm? There is a particular script I will like to run, but it can not be .htm. Thanks David No. -- Tom [Pepper] Willett Microsoft MVP - FrontPage Since 1997 --------------------------- "116" <116@discussions.microsoft.com> wrote in message news:52CEB706-E273-461F-9DD9-483EBA3C1F57@microsoft.com... : Can 'Shared Borders' be .asp rather than .htm? There is a particular script : I will like to run, but it can not be .htm. : : Thanks : David The shared border files must be .htm - left.htm, righ...

listview insert question
I am really having a time determining what is wrong with the inserting from a dropdownlist inside a listview. Everything else is working regarding the listview, except the insert. But, there must partially working as there is a new row added to the table, just not with information. When I add SelectedValue='<%# Bind("ApplicationItemID") %>' to the droplist I get an error. The error is as follows; "Databinding methods such as Eval(), XPath(), and Bind() can only be used in the context of a databound control." I have an insertcommand a...

"newbie" question using exchange
we have a new install of exchange using sbs 2003. What we would like to do is setup mail folders for three different user accounts that are accessable by all users. I would like for mail that is sent to these accounts (eg: orders@companyname.com) to be sent to the appropriate "public" folder. Could someone give me some direction on setting this up? Any alternate suggestions would be appreciated. Here is a link that explains how to do what you are looking for: http://www.msexchange.org/articles/MF021.html The link relates to Exchange 2000 but the steps are the same. -- Mark Fuga...

Counting days
Have the following fields in a table: Date,Name,JobNo,Hours. I need a SQL query to count the number of days each Name appears over a period of days. Any Name can appear more than once on any Date. If a Name appeared three times on the same Date it should only count as one day. Any suggestions or help would be appreciated Thanks Phil_mac SELECT name, COUNT(*) FROM (SELECT DISTINCT name, [date] FROM tableName WHERE [date] BETWEEN firstDate AND lastDate) AS a GROUP BY name where firstDate and lastDate are parameters defining the range of date of your interest. ...

Setup new acct question...
I am trying to add a new bank account in 2004. When it comes to assigning a FI I am only given the choice, in a drop down list, of the accts that have previously been set up. How do I manage to get a new bank name assigned? tia In microsoft.public.money, John h wrote: >I am trying to add a new bank account in 2004. When it comes to assigning a >FI I am only given the choice, in a drop down list, of the accts that have >previously been set up. How do I manage to get a new bank name assigned? AccountList->SetUpAccounts->AddANewAccount and type the name of the bank int...

Counting Number of Identical Entries in a Spreadsheet Column
I am using XP Home and Office XP. I use a spreadsheet that has about a hundred or so lines and a number of columns, one of which is "Agent Number" in which a four digit number is entered to identify the Agent for that particular line item. Is there an easy way for me to create a table showing a ranked order of the number of times each Agent is listed in the column (e.g., Agent 7818: 20, Agent 7820: 17, etc.)? Thanks in advance! -- Fred 2002 Hi use a pivot table for this . See: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.c...

Display count of characters in a cell while typing.
Not sure if this should be here or in the worksheet.functions newsgroup but I think here is a safe spot. As a user types text into a cell I would like to have another cell display the number of characters that are currently in it. The count has to be 'live', meaning that as the person is typing the counter is updating, not simply updating after the user has pressed 'Enter'. Alternately, a count-down counter would be even nicer ;-) Suggestions? Search brought up nothing. -- Toby Erkson Oregon, USA Excel 2002 in Windows XP Hi not really possible as macros don&#...

A question about CCommandLineInfo
I want to write an application that if it is given no command line arguments, it will run in a GUI mode. However, if the user passes any command line arguments, it means that he wishes to execute the program in a "automatic" mode, therefore I want it to in this case run and eject information to the console. I want it to be a single application to help reduce redant code. - Christopher Dannemiller. This is a common question. I've never seen a good answer for it, mostly because I think the idea basically is a Bad Idea anyway. joe On 3 Jun 2005 15:54:00 -0700, chris.dannemil...

DISP_FUNCTION_ID and ON_EVENT question
Hi, When I used: AfxConnectionAdvise (pSrcUnk, __uuidof (ISomeInterfaceEvents), GetIDispatch(TRUE), TRUE, &m_cookie); in a CCmdTarget derived class. I expected that I will be able to use ON_EVENT but it didn't work but DISP_FUNCTION_ID. Can any one explain this and what is the difference between DISP_FUNCTION_ID and ON_EVENT Thanks in advance ...