#### Help with Lookup/Sum Formula

```I have a spreedsheet with this type of data, for example

Product Group    Quantity        Size
2812                       10               0.75
2812                       100             0.75
2812                       300             0.5
2817                       100             0.25
2817                       200             1
2845                     1000            1.5

I want to be able to set up a formula that you enter a product grou
(2812) in a cell and it will for that product group go and sum up al
the quantities for each size.  The formula would go in the Sum colum
For Example

2812

Size            Sum
0.375	      2,342
0.5		2,342
0.75	       1,992
1		 883
1.25	       284
1.5		90
2		 290
2.5		16
3		 32
3.5		0
4		 26

So far i have had no luck with figuring out a formula that wil
accomplish all this, any help would be appreciated.

Thank

--
-----------------------------------------------------------------------

```
 0
9/26/2005 7:18:52 PM
excel.misc 78881 articles. 5 followers.

1 Replies
286 Views

Similar Articles

[PageSpeed] 56

```I have used your numbers and set them out in a table A1 to C6

I then set up a table with the possible sizes in the left han
column(A12 down)
and the possible product groups in the column headers in B11 an
across

entered in B12

=SUMPRODUCT(--(\$C\$1:\$C\$6=\$A12),--(\$A\$1:\$A\$6=B\$11),\$B\$1:\$B\$6)

note the \$ signs especialy for \$A12 and B\$11

this makes it possible to extend the formula down the columns an
across

good luc

--
bill

-----------------------------------------------------------------------
bill k's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=82

```
 0
9/26/2005 9:13:43 PM

Similar Artilces:

help with database design required
Hello Everyone, I have a system that has to track the movement of tools from in and out of the stores. Borrower Details: tblname: id-PK,name,department tbltoolmaster ToolID-PK Toolno ToolDesc Location tbltooltransaction Tranid-PK TrantoolID - FK NameID - FK DateTaken DateReturned what i am attempting to do is for every tool borrowed from the tool store i need to be able to record the date taken and date returned. If the date returned is not null - Tool is Available Once the tool is burrowed by a person A on a given date than the status should be updated to loaned and when he returns ...

Help With Formula #5
I have data in a worksheet A B C 1 Verify 2% 99% 2 Audit 2% 98% 3 Account 5% 97% 4 Forward 4% 96% 5 Place 5% 95% I have another cell (F1) in the sheet that i would like to list the text in A1, but only if B1 is at least 5% if not then I want it to list A2, but only if B2 is at least 5% and so on down to A5. Please Help. What happens with row 3 and row 5? -- Don Guillett SalesAid Software donaldb@281.com "Report Lackey" <anonymous@discussions.microsoft.com> wrote in message news:033b01c3d62d\$ebd800e0\$a401280a@phx....

How do I set up a weighted formula in Excel?
Can someone help me please? I need to do a weighted formula in Excel 2007, where the total of the numbers equals 90% of one number and 10% of another number. Is it possible to do this? On Mon, 10 May 2010 15:53:01 -0700, ib1qb <ib1qb@discussions.microsoft.com> wrote: >Can someone help me please? I need to do a weighted formula in Excel 2007, >where the total of the numbers equals 90% of one number and 10% of another >number. Is it possible to do this? > =sumproduct(numbers, weights) e.g. =sumproduct(a1:a2,{0.9,0.1}) --ron Sa g1 to j7 conta...

Sum of "Visible" controls
I have a form which has 4 controls that I need to sum: [Subtotal] (subtotal from subform), [GST_Amount] (calculates GST, is hidden if GST checkbox is not chedked), [PST_Amount] (calculates PST, is hidden if PST checkbox is not chedked), and [HST_Amount] (calculates HST, is hidden if HST checkbox is not chedked). Is it possible to create a Grand Total textbox that will sum the Subtotal and only those controls that are "visible". Or, alternatively, can anyone suggest a better way of calculating these amounts (i.e., if GST checkbox is not checked, then "0")? Any ...

Word wrap macro deletes formula
I created a macro to wrap text on a protected worksheet in which each cell contains a formula importing text from another worksheet. The cells are locked and formulas hidden but when I run the macro, it wraps the text and deletes the formula> how can I stop it doing this? The macro is as follows: Sub Wordwrap() ' ' Wordwrap Macro ' Macro recorded 01/10/2004 ' ' Keyboard Shortcut: Ctrl+w ' With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False ...

Help requested in formulating Functions and script to create a mat
Visual Studio.net 2003, SQL Server Report Designer, SQL Query Analyser. I have a table of milestones with data such as project, miletone name, baseline date, planned date and actual date. My task is to produce a chart that has months in the columns and for each month a set of blocks of data, made by concatenating fileds form the table, for milestones due in that month. I can extract the 'blocks' of data easily enough but when I put them in columns each successive month starts on a new line below those of the previous month. I need it so that the first milestone of each ...

I have installed the Business Data Lookup add on which is supposed to allow CRM data to be inserted into office documents such as a Word file. Inside of Word 2003, I see a "Insert from CRM" button, but when I click on it, nothing happens. It is just clicked, but no options come up after that. Is this a bug? --- Brandon Office Equipment & Supplies http://www.presentationsdirect.com I think it is. I experienced the same. On top of that it will slow down MS Office heavily. Forget it. "Brandon" wrote: > I have installed the Business Data Lookup add on which...

Struggling with formula
Hi I have a spreadsheet withthe following data: 28/08/2008 09/05/2008 HINGE SA28553 8 =A31.10 =A30.77 =A36.16 28/08/2008 09/05/2008 HINGE SA28566 8 =A31.10 =A30.77 =A36.16 20/08/2008 28/08/2008 HINGE SA28389 24 =A31.10 =A30.77 =A318.48 14/08/2008 21/08/2008 HINGE SA28268 12 =A30.00 =A30.00 What I need to see is the total number of units, of which XXX are Free of Charge between xxxx date Sorry, but I am struggling and got so far =3DCOUNTIF(J2:J924,"=A30.00") but struggling with the rest, can anyone help Ta BabyPink, In general: =SUMPRODUCT((FirstDate<=Date"))...

Need help with the query.
How can I get desire results below. I cannot seem to figure this out. Any help is greatly appreciate. IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL DROP TABLE #Temp GO CREATE TABLE #Temp ( UserHistoryId INT NULL, UserId VARCHAR(6) NULL, EventDate DATETIME NULL ) GO INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (664, 'User1', CONVERT(DATETIME, 0x00008eac00000000)) INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (1014, 'User1', CONVERT(DATETIME, 0x00009c2600ef152a)...

Trouble with copy and past of formulas in Excel
I got a sheet from one of my collegaes that was causing problems, it sometimes looses formulas (It looks like a problem with connections through our network) and you get true untrue name errors, after refreshing the cell the problem is gone. Now I'm having trouble with copying formulas, after trying some copy and paste it crashes with dr. Watson. It seems that there is some timeout problem. More people have trouble with this with the somewhat larger sheets. ...

Help with Lookup/Sum Formula
I have a spreedsheet with this type of data, for example Product Group Quantity Size 2812 10 0.75 2812 100 0.75 2812 300 0.5 2817 100 0.25 2817 200 1 2845 1000 1.5 I want to be able to set up a formula that you enter a product grou (2812) in a cell and it will for that product group go and sum up al the quantities for each size. The formula would go in the Sum colum For Example ...

Countif formula
Hi, I'm new here and I hope I'm posting in the right forum. I require help with a formula that has left me scratching my head for a couple of hours. I have a column of data cells that lists 3 different options, for example: option1 = Good option2 = Ok option3 = Bad i need a formula that will count how many 'Good' entries there are and to work it out as a percentage of the total number of entries (all 3 options). I hope that makes sense. If more info is required, please let me know. Thanks for your time -- godonlyknows --------------------------------------------------...

List Box
I am stumped and need some assistance - PLEASE. New to List Boxes. I have created two unbound list boxes on an unbound form to use as criteria for a report. Both list boxes (lboxCategory and lboxSupplier) have two fields CategoryID and Category and SupplierID and Supplier sorting ascending on latter fields. The Multi-Select property is set to Extended. I have a command button cmdPreview that I would like to use to preview the report after selections are made. I will select two or more choices from each list box at a time. Can anyone get me started with the code I need to make this h...

Lookups #2
I am trying to write a formula which looks up a product code in a list, and then references a date to pick out a piece of information. The code is in a horizontal list with the dates being in a vertical list across the top of the page. eg Code Dates Jan Feb Mar 111 50 70 80 112 90 10 60 113 60 10 80 It needs to refer to the code then the month, to be able to select the correct cell. eg code 112 in February = 10 Is there a function to cross reference the two pieces of information to select...

Formula to display Count of Days in given month, using list of Start and End Dates
I have a large number of rows that contain a start date and end date. I am attempting to generate a count of days within that date range that are in a particular month, for example, Jan of 2011. Any suggestions for a formula that could be used to display the amounts shown in Column C would be helpful. Layout is: A B C Start Date End Date Count of Days in Jan2011 1/10/11 4/7/11 21 2/6/11 3/1/11 0 12/20/10 2/15/11 31 Etc. Dave, I think the best way is to create a table f...

Need a formula that tags one table based 2 columns in each table
I have two tables on one worksheet, Table A and Table B. Each table contains two column with X and Y coordinates, all values are numerical. The coordinate system is irrelevant. I need to "tag" all XY coordinates in Table B that have a matching XY coordinate in Table A. If Table A coordinates are in columns A and B, and Table B coordinates are in columns C and D, then I want to place the text "hit" in column E next to each coordinate pair from columns C and D that match a coordinate pair in columns A and B. The ranges for the respective tables have been named ACOORD and...

Help #6
I am a student I Niagera college and am working with excel, I have a n ifstatmet that is giving the correct answer but its not solving the anser. The statment : =IF(G9>B3,+"sum(G9*C3)",+"sum(G9*1)") The Answer sum(G9*C3) The problemi is that it is the wright answer but it will not tally the sum. Help me PLZ How about: =IF(G9>B3,G9*C3,G9) the use of the SUM function is wastefull (as there is nothing to sum), as it is the multiplication by 1. Regards, KL "Niagera College Student" <Niagera College Student@discussions.microsoft.com> wrot...

Help with SQL- SupplierList cost into Item cost field
Hi, re-posted! See below. Hi, thanks for that- it worked perfectly. There is just one snag however and I didn't think of it until I was testing. Many of the suppliers use specific currencies so the information I need copied from the Supplier tab is actually the Local Cost. When I view the SupplierList table, Local Cost doesn't appear as a column. Is it just a calculation based on exchange rates for information or does it actually exist somewhere within the database? Your help once again would be much appreciated. T. "convoluted" wrote: > Hi Tara - backup your...

Worksheet Help req PLZ
Hey All, I have a small issue where I have created a worksheet called data and in Cell A3 I have a linked cell to another worksheet and cell "WK48!A2" In Cell A4 in the "data" worksheet I need a link to "WK49!A2". I would like to link all cells in Column A to consecutively "WKxx" worksheet once I add them to the workbook. I thought I could drag them mouse down but al I get is links to consecutive cells in "WK48" work sheet. Please help mag()() Try =INDIRECT("'WK"&ROW(48:48)&"'!A2") -- Reg...

Date Formula #3
I am having difficulty with the "NOW" formula. What I would like for it to do is when I enter a value in one cell have it update in another cell the date when the information was entered and that date stay permanenty. What I am having is the date changes to the current date everyday. Is this possible to do? Any help would be appreciated. Thanks. Todd You can do it with the change event of the worksheet This example will place the date/time in the B column if you change a cell in the range A1:A20. Place the code in the Sheet module Right click on a sheet tab and choose ...

Can anyone help with this chart?
I would like to have the following values/labels on the y and x axis of any style of chart: X-axis: July, August, September etc for a full financial year; Y-axis: FB, AP, DR, LA, PC. OR the other way around. Presently the Y-axis simply displays the labels 'Jan Jan Jan Jan Jan' etc. Thank you. Any help would be much appreciated. Check out Jon Peltier's article on creating a vertical category axis: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "A little stuck" wro...

How do I make this formula?
I have an Excel worksheet to track my 401k performance. I have a row for Date, the Balance corresponding to each date, Contributions, a formula to calculate my Total Contributions, and a formula to calculate my Total Gain/Loss. My question is, how to I get my Gain/Loss formula to update corresponding to a new Date/Balance? Is there a formula function to make the formula ignore Column data in the previous Rows? Thanks! There will be, just add/subtract items from that row. Can you show us an example with data. -- HTH RP (remove nothere from the email address if mailing direct) &...

HELP! CFtpConnection GetFile cause memory leaking, WHY?
I'm trying to use CFtpConnection GetFile to download files from a WS_FTP server. I found if I download 1000 files (Size from 17KB to 25KB ), it can cause about 10M memory leaking. When I commented out the GetFile( ) line, there was no memorry leaking. WHY? Following is part of my source code: try { // Request a connection to Image Server //Use default FTP Port //Use Passive MODE pConnect =3D sess.GetFtpConnection(strISIP,strFtpUser,...

Which is faster sum(if) as an array or sumproduct?
I finally got my sum(if) based on more than one condition to work but it takes my 1.8 Ghz chip 7 or 10 minutes to calculate the workbook with the array formulas in 2003 Excel. Is Sumproduct any faster or do I just need to use the free time to do something else? Thanks, Lee It depends. At this link: http://msdn2.microsoft.com/en-us/library/aa730921.aspx there are some VBA routines that will let you time the calculations. You'll find the code about 1/4 of the way down. Biff "Neophyte" <wleecoleman@.nospam.ev1.net> wrote in message news:OqrP4rOcHHA.4260@TK2MSFTNG...