Multiple Parameters In Sum Product

Oct 18, 2011

Im currently using the below formula but was wondering if its possible to combine the parameteres that im looking up in coulmn L in sheet one so i dont have to do the formula three times? something along the lines of={"P11111";"P22222";"P33333"}?

=SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P11111"))+SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P22222"))+SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P33333"))

View 5 Replies


ADVERTISEMENT

CountIF Multiple Parameters

Jun 18, 2009

I have a long list of past jobs, around 4000+. I have multiple fields, but I really wish to concentrate on the:

1) Job Type - Example would be Medical or Imprint

2) Job # - Correlates when it was done - Example would be 91059 would be a job within 2008-2009 fiscal year.

All the jobs are listed in the first spreadsheet. The second spreadsheet will hold generalized data broken down by the Type of Job and the fiscal year it was done.

I wish to first count how many jobs fit a specific job type. This was easily done:

=COUNTIF(Data!A2:A4121,A7)

A7 = Medical
A1:A4121 = the range of the names.

Count comes up as 346, which is correct.

I then want to add another parameter to break up the 346 by fiscal year. It ranges from 05-06 to 08-09. I came up with this:

=COUNTIF(Data!A2:A4121,A7)-COUNTIF(Data!F2:F4121,"9????")

Since the job # correlates to the fiscal year it was created, anything starting with 9 is a job done in 08-09. Anything starting with an 8 would be of course 07-08 range. I would have multiple fields with each different fiscal year.

However, when I put the 2nd formula in the function didn't work. It keeps the count at 346, which I know is wrong. I am not sure if I did something wrong here. Been looking at this for an hour and can't figure out what is wrong. The jobs that are medical within 08-09 fiscal year should be 120, but it keeps at 346.

View 14 Replies View Related

Conditional PRODUCT Function Formula (return The Product Of A List Of Numbers In A Column)

Sep 30, 2009

I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:

Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula

The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:

{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}

This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.

View 2 Replies View Related

Multiple Criteria Parameters In SUMIFS

Apr 29, 2014

I have a below table as below

A B C
a aa 1
a aa 1
a dd 1
a aa 1
b aa 1
b bb 1
b aa 1
b bb 1
c cc 1
c bb 1
c bb 1
c cc 1
d cc 1
d aa 1
d bb 1
d cc 1

When i put the formula

=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"}))

it returns 12 However when i put

=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,{"aa","bb"}))

it returns only 5

I don't want to use multi formula like

=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,"aa"})) + SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,"bb"}))

I want to achieve this using SUMIFS only

View 5 Replies View Related

Formula With Multiple Lookup Parameters

May 8, 2009

In the attached file I have a sheet containing my data in A1 - D73. Column A contains a list of names, Column B contains a specific month, Column C contains a specific category and Column D contains the raw data.

Is it possible to create a formula similar to VLOOKUP to look not only at Column A, but to look at Column B as well in determining the value returned? I would like other users to first select a name and then select a month to view the data. I've attached a sample of what I've created so far. The original file contains 14 Names, 9 Months and 40 Categories.

View 3 Replies View Related

Microsoft Query Multiple Parameters

Mar 12, 2014

I would like to have set of data as Parameter if possible.

Example: In Microsoft Queries I have two data sets. In first one shows let's say Item Number, and second one I should have table with components of all Item numbers listed in first table.

So first table I set one Parameter (?) and i get like 8 results. I would like that second table takes those 8 results (sometimes more or less then 8) as parameter and shows data (components) for them.

If I set sign '?' I can only select one cell as parameter, is there any way I can select multiple cells as parameter and use it like SQL function IN ?

Code:
SELECT 1.ITMNR, 1.COMNR, 1.QTYPR
FROM 1.1.1 1

WHERE 1.ITMNR IN ?

And second table should look for results in first table under 1.COMNR and select it as 2.ITMNR

Code:
SELECT 2.ITMNR, 2.COMNR, 2.QTYPR
FROM 1.1.2 2

WHERE 2.ITMNR IN ?

View 4 Replies View Related

Dynamic Named Range For The Product Name On The Product Details Sheet

Dec 24, 2008

I'm working on a order spreadsheet system, and I have one sheet called Product Details, where the product name, list price and product code are found, these link to the Sales order page, and I need them so that they can be added to.

So far, I have created a dynamic named range for the Product name on the Product details sheet, and linked the the range via a list validation on the Sales order sheet.

The drop down list displays the products and can be added to by typing new product names on the other sheet.

What I need now is that when a product on the sales order page is selected, it draws the the list price and product code data automatically from the sheet, I tried using a normal vlookup, but I couldn't get it to work. I also need the list price and product code columns to be 'dynamic' so new values can be added further along.

View 5 Replies View Related

Multiple Parameters Using A Range Of Cells To MSQuery

Apr 17, 2003

how to pass multiple parameters using a range of cells to MSQuery? When I try to it tells me that I can select a single cell only. Anyone know of a new and improved sql driver to use with Excel?

View 9 Replies View Related

Macro For Name Generation From Product To Product With Sizes And Filenames

Jan 30, 2014

I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.

View 4 Replies View Related

Import Multiple Text Files Based On Parameters

Mar 11, 2008

I get about 10 text files at the beginning of each month that I need to import into Excel. The file layout does change slightly from month to month so I have an Excel spreadsheet with the layout as the first worksheet. Each text file is in fixed layout form rather than delimited. The structure of that page is that the name of the first text file is in cell A1. The cumulative field widths are below that and the data formats are next to the field widths (in the adjacent column “B”). T=Text, G=General, D= Date with 3 flavors of Date: YMD, DMY and MDY. For the second text file, it repeats the structure in columns “C” and “D”, the third text file in columns “E” and “F” etc. What I would like is for the macro to open the first text file (based on the name in cell A1) and import it into a new worksheet (same file) using the cumulative field widths in A2 through A8 and the formats from B2 through B8, then import the next text file to a new worksheet (same file) using the cumulative field widths in C2 through C8 and the formats from D2 through D8 and keep going until all the files have been imported. The sheet names should be the same as the file that is imported (the “.txt” part of the file name isn’t important). I’ve attached a copy of the Excel spreadsheet that has the file names, field widths (cumulative) and field formats and a 2 row sample of the text file for the first import.

View 6 Replies View Related

Sum Product With Multiple Criteria

Jun 8, 2014

Data below, I would need a formula for the sum total of the volume multipled by the price for each product category.

Category
Product
Price
Vol1
Vol2

HW
Prod 1
12
2
5

[Code] ..........

View 7 Replies View Related

SUM PRODUCT Multiple Conditions Sub Totals

Mar 10, 2014

I have a sumproduct formula that does a count of my table column based on 2 conditions. However, when I filter my data it still present the same results. So ive been told to use a Subtotal formula. I have scoured the net for the right formula and have come up with the following... though it returns data - it returns the wrong results.

Anything wrong with the below subttotal - sumproduct formula? for us in filtered data sets?

Code:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(TasksTable[HIGH LEVEL],ROW(TasksTable[HIGH LEVEL])
-ROW(Tasks!$K$20),0,1)),--(TasksTable[BD]=999),--(TasksTable[HIGH LEVEL]=GR15))

View 3 Replies View Related

Sum Multiple Product For Multiple Values

Apr 30, 2009

is it possible to use the sum product formula to give me the total results for multiple values?
I know I can ask it to look at column a for a value of e.g "44" and column B a value of "gin2334" but can I ask it to ge me total for values from column A that equal "44, 45, 46, 47" ans B "gin2334"?

if not has any got a clue as to how i would get these results?

View 9 Replies View Related

Finding Keywords To Identify Product In Multiple Cells

Jul 23, 2012

Macro - it is currently set up to find a specific text, and activate that cell. From there, the spreadsheet has data in 6 cells in the 6 rows below the active cell. What I need the macro to do is to identify the product based on key words in a range of cells: that is, if any of those cells contain the string "total", than the product type is total, but if "total" is not found, look for keywords such as "alpha" and "beta", to identify the product type. If its not a total product, then i want it to look for "productB", "productc", and so on with relevant keywords until the product is identified.

I think IF(ISNUMBER(SEARCH can be used, but I'm not sure how, and I'm not sure how to search all 6 cells (to make things worse, the number of cells vary, so I'm using the "find" function to figure out the first and last cell that contain data).

View 5 Replies View Related

SUMPRODUCT Quarterly Average -> For Multiple Product Categories

Dec 31, 2013

I have data table with sales numbers by product, its wheel base, and over platform for months ranging from Aug 2013 - Nov 2013. This data table will populate with the complete month sales numbers after the month has finished going forward.

I have a table to the right of the listing the sum totals for each product (by wheel base and platform) by each quarter (rolling).

Now I need building a sumproduct average to calculate the quarterly average. This is not that simple because not all products (platform/wheel base) were available the entire quarter.

Also PLATFORM 3 in the tables are grouped - instead of listing platform 3a and platform 3b - using this in the formula:

(LEFT($D$2:$D$100,LEN($J9))=$J9)

Because not all products were available for the entire quarter - and the formula will need to count how many months that product, platform, wheel base was available during that quarter I'm guessing "=MOD(MONTH(A1)-1,3)+1" this may have to be used to count the number of months into the quarter the product was available.

Attached file. [URL] ........

View 9 Replies View Related

Multiple Product Order Form With Price Lookup

Feb 26, 2009

On an order form the customer will be able to enter the width and projection of several products that they want to order. The way these products are priced are in a Matrix of Width over projection. The current system i have designed is:

1. The pricing Matrix's have all been put into one big table and given a unique id per product matrix.
2. An advanced filter has then been run and and it extracts the appropriate matrix and copies it onto the process sheet.
3. An Index/Match formula is used to find the price for the inputted width and projection. It does this by finding the intersect point of the width and projection on the table.

Currently this will be put into a macro and assigned to a button.

The problem is that up to 15 (or possibly more) products need to be able to be ordered in one order form. With the current system it means there will be a lot of Advanced Filters and there will be a macro button that will need to be clicked after every product order (and they're could be 15 or more). Obviously this isn't very professional, it is time consuming and must be the hard way of doing it.

I was wondering what over systems that could be used for this sort of thing. The more solutions there are to this problem the better. Attached is the file. If you do post a solution it would be preferred if you could also post a file with a working example in. I find it much easier to learn the solution if i can see it working.

View 9 Replies View Related

Product Look Up: Look Up A Product Based On The Number And The Qty

Feb 20, 2009

i need to look up a product based on the number and the qty. see the example attached.

On tab "Printer - S" 1 need to lokup the costs based on the product code 8 and the qty 100. How do i do this and make it adjustable for any range of priduct codes and qtys. Again, becuase this will be used on a Mac Office, i cannot use macros.

View 4 Replies View Related

MS Query - Parameters (parameters On A Query But Use A Wildcard To Return All Instances)

Nov 17, 2009

Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.

View 4 Replies View Related

Type The Product Id In The A Column Of The Orders Sheet(Sheet2) To Auto Insert The Product Name In The B Column

Jul 16, 2006

In the first sheet I have two columns, one for the product_id and one for the name of the product. So the Sheet1 is like a small database. The second sheet is for the orders.What I want is when I type the product id in the A column of the orders sheet(Sheet2) to auto insert the product name in the B column so i dont have to write it every time.

View 2 Replies View Related

Cell Referencing: Enter A Product Name In One Cell And Have The Price For That Product Automatically Appear In Another Cell

Feb 3, 2009

I'm trying to create a supply order form. Is it possible to enter a product name in one cell and have the price for that product automatically appear in another cell? Is there a formula that I can use to make this happen?

View 2 Replies View Related

Multiple Lookup: List Of Part Numbers Based On The Product Part Code

Feb 12, 2009

I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.

=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})

so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.

View 3 Replies View Related

Add 0 If Parameters Are Met?

Mar 14, 2014

I am trying to add a 0 if it is missing at the start of the number in columns K2:K3000 and L2:L3000, The numbers do not have fixed lengths so I cant use the custom setting but if the number starts with a 7, 8 or 3 I want to add a 0

View 6 Replies View Related

Looking Up Value Based On Two Parameters

Apr 1, 2014

Excel Question 1.jpg

I need to have a code that looks up Column 1 (Parameter 1) first, then looks up Column 2 (Parameter 2), and inputs Column 3 (Value). The code needs to be in a SINGLE cel.

I thought initially using a Lookup function within another Lookup function might work, but I can't seem to get the code to work properly.

View 7 Replies View Related

Looking Up Based On Two Parameters?

Dec 7, 2013

I'm working on an attendance tracker for church. We will have folks scan a QR code and fill out a 2 question survey: Name of person being checked in and date of check in. Both are mandatory and are set in such a way as to always have the same formatting every time. That's my first sheet, it just gathers the data. My second sheet actually has the attendance tracker with the roll and dated columns. What I'm trying to create on the second sheet is a formula that will search the first sheet for the date and name and return a 1 if they were there that day.

View 7 Replies View Related

SUM Within Range Of Parameters

Oct 7, 2013

I have a datast that looks like:

ID Week Sales
1 1 $200
1 2 $300
1 3 $200
1 4 $250
1 5 $220
1 6 $100
2 1 $500
2 2 $100
2 3 $230
2 4 $250
2 5 $210
2 6 $110

I will have 3 parameters in different cells that will identify the range of data in which i'd like to get a SUM for:o

ID,
Week,
Number of weeks.

So for example my I would identify ID =1, Week = 3, # of Weeks = 2

I should get an output of: $450 ($200+$250, rows 3+4 from above).

I've tried using IF(AND and Sumifs nested.. but it's not working out..

View 3 Replies View Related

IF Formula. I Need 2 Parameters

Jan 10, 2007

I am using the following formula and I need to modify it a little:

=IF('Input Sheet'!A17="AR",0,'Input Sheet'!F17)

All I need to do to the above is add "MISC" to the formula. I have tried but I keep getting errors.

Basically I want:

=IF('Input Sheet'!A17="AR" OR "MISC",0,'Input Sheet'!F17)

View 9 Replies View Related

MS Query With Parameters

May 22, 2007

Is there any way to use an Excel cell as a parameter for MS Query.

i.e. Select fields from table where field = "cell value"

I tried the $ ($D$5 ) got Invalid pseudocolumn error; which leads me to hope there might be a way to introduce a VALID pseudocolumn in my Query.

View 9 Replies View Related

MS Query Parameters

Feb 4, 2009

I have a query that has criteria based on "is one of". I would like to pass this off to a parameter selected from a list box where the user could select multiple items. Is something like this possible?

View 9 Replies View Related

Altering Parameters For IF Formula?

Mar 31, 2014

I need to modify the following formula,

=IF(G5=-105,-105,-G5-IF(ABS(G5)>251,40,IF(AND(ABS(G5)>221,ABS(G5)<251),30,
IF(AND(ABS(G5)>180,ABS(G5)<221),20,IF(AND(ABS(G5)>=100,ABS(G5)

[Code].....

View 10 Replies View Related

How To Search For Parameters From Database

Dec 26, 2013

I need to search for 3 parameters, L, M and P based on the *** and age from the attached excel file wtageinf.xls and enter them into the formula below

((X/M)**L) - 1
Z = -------------------------, L≠0
LS
or

Z = ln(X/M)/S ,L=0

where X is the weight of the child. The formula is for the Z score.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved