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
ADVERTISEMENT
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
Oct 30, 2013
Currently trying to calculate the cumulative performance for a range of months. For example, I have the below figures:
Jul-12
6.59%
Aug-12
1.13%
Sep-12
2.61%
Oct-12
-3.56%
[code].....
I want to create a product if function where if I'm looking back 6, 12, 18 months etc from a specified month (e.g. Sep 2013), it will calculate the product for the range of % (e.g. Apr-Sep for 6 months, Oct 12-Sep 13 for 12 months, etc...). I would imagine it will be =product(1+if(Column A range=6, Column B range))-1.
View 3 Replies
View Related
Sep 4, 2008
I'm trying to do:
Column A is full of daily consecutive dates, Columns B to H are full of numbers.
I'd like to create function that accepts 2 dates and a column reference as input like this:
function(date1, date2, columnnumber)
And as output it gives the product of all the numbers in that referenced column between the two dates.
View 9 Replies
View Related
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
Mar 10, 2014
See attached spreadsheet. I am trying to create one formula that will take the product of numbers in column B based on criteria in column A (want the product of %s in each year). Product if formula.xlsx
View 7 Replies
View Related
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
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
Jan 29, 2009
I have two columns of data. for example:
Column A
1
3
4
0
2
0
0
3
Column B
Product A
Product A
Product B
Product B
Product A
Product A
Product B
Product B
What I need the function to return is:
Product A - 3
Product B - 2
This is what I thought the function should look like but it is not working:
=IF(A6:A50>0,COUNTIF(A6:A50,"Product A"),0)
If Product A or B did not have any values >0, it would return a zero.
View 4 Replies
View Related
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
Mar 3, 2009
Maybe I should say conditional product of conditional sum. Eitherway, the values in column J are summed if P is column R for the repective row.
View 4 Replies
View Related
May 27, 2014
What's the functions can figure out on the below conditions, For example :
PRODUCTSYEARQTY
AAA 2015 2
ABB 2011 3
AAA 2013 8
BBA 2011 6
AAA 2011 4
BCC 2014 1
Conditions:
1) looking for AAA product
2) sum the qty is before 2014
The answer is :12
View 5 Replies
View Related
May 12, 2009
I have a large dataset (24000 rows) that requires me to multiply two different columns of integers. In some cases, the two integers are both negative and multiplying them results in a product that is positive. I actually need that product to be negative rather than positive. I can't quite seem to figure out the best way to accomplish this.
View 5 Replies
View Related
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
Feb 28, 2014
I have a table that has:
in column a an number 1,2,3,4,5,6
in column b I have a code against each number
in column e I have numbers , 1,1,1,1,1,2,2,2,2,3,3,3,4,4,4 etc ....
What I would like to have is the code from column c replicates in column d that's matches the code from column c
I have attached a sample of what I need : Product Detail - Copy.xls‎
View 14 Replies
View Related
Sep 11, 2009
I am using the following formula to calulate the compounded rate of return for a series of monthly returns: {=(PRODUCT(1+(b1:b100)/100)-1)*100}
I would like to be able to integrate a VLOOKUP function into the above formula so that it looks at the dates in column A and then apply the PRODUCT function above to the return values in column B that fall between start and end dates specified in cells C1 and D1.
View 4 Replies
View Related
Dec 8, 2009
I receive montly investment performance projections going out monthly for 10 years. I need to show the return annually for each year. I created a SUMIFS formula to sum the percentages between two date ranges (beginning of the year and the end of the year for each year). However, my SUMIFS formula does not take into considersation compounding. How can I modify the SUMIFS formula I created to take into consideration compounding?
I know how to convert monthly percentages to an annual percentage,using the PRODUCT formula =PRODUCT(1+T15,1+U15,1+V15,1+W15,1+X15,1+Y15,1+Z15,1+AA15,1+AB15,1+AC15,1+AD15)-1, whereas T15:AD15 is the monthly percentage gain. Is there a simplier/alternative way that I can incorporate into the SUMIFS formula?
If it is too complicated to utilize a SUMIFS formula, I would like to be able to select a range of cells and have Excel calculate the result vs typing "1+[select the cell]," for each month. It takes me forever and open to input errors. It would be much easier to be able to select a range of cells when creating the formula.
View 3 Replies
View Related
Aug 5, 2014
I created a large table with data (1700 lines long) and am looking to create a sheet that will use it to do a call for totals whilst filtering for a specific line.
This is what I have currently, filtered by delivery date and key ("furniture desc").
The 58 and 54 are totals for that particular product which I would like to ideally see on a separate sheet.
result1.JPG
result 1a.JPG
What I would like to achieve is this: result2.JPG
What kind of formula would I need to use to first filter by date, then by key, then to tally up the quantities and drop them on another sheet?
Bearing in mind that other keys are intertwined on the Y axis so I can't use =SUM(xx:xx)
View 2 Replies
View Related
Jan 3, 2010
I have a very large spreadsheet, a few columns and rows I attach..
in col a we have the time of the event..in column b i have a numerical rank for the
importance of factor 1,in column c i have similiar for factor 2.
What I want is the correlation for each event between col b and col c using the rsq function..i have no problem using this function..but i need the result for each event produced in col d in this same sheet and printed on each row and since I have thousand of rows i need a formula I can fill down
see attached sheet
View 5 Replies
View Related
Feb 23, 2010
I have been using the sumproduct formula to count how many times a certain piece of data appears...which works fine...for text purposes such as names, initials etc...
I now need to amend the formula to count how many times a date appears in a range of dates.
I know when you use the sumproduct...dates must be input and formatted as text.
example
A1
01-Jan
02-Jan
5-Feb
7-Feb
12-Feb
formula to only count dates with feb in?
View 12 Replies
View Related
May 31, 2007
I have a spreadsheet and it shows a raw data dump of all sales transactions per product. This has been transferred via ODBC and I need some sort of query to filter out the data.
Is there any way I can write a query to only bring in the latest transaction based on the sale date per product?
Columns are:
Product Code|Last Sales Date|Cost Price
View 9 Replies
View Related
Mar 26, 2008
I have a Sumproduct formula that should be resulting in 0. However, it is resulting in a very small number. I have 2 columns of data. The first one contains -169.5, -123.4, -34, -67.4, 169.5, 123.4, 34, and 67.4. The second column contains all 1's. When I create the following formula, =SUMPRODUCT(H27:H35,J27:J35), it results in 5.68434E-14.
Sometimes, when I have different numbers in the first column, but it still adds up to 0, the Sumproduct works.
View 12 Replies
View Related
Aug 29, 2008
What I need to do is count the number of cases and values of everything written between the first of January and today (242).
I know how to do it by counting the days from the 1st of Jan to today but how can I do it so it automatically updates each day.
This is what I have at the moment.
The product name is in K, the date is in H and the value is in M. The range is from cell 7 to cell 94
=SUMPRODUCT(--($K$7:$K$94="Pension"),--($H$7:$H$94<=TODAY()),--($H$7:$H$94>=TODAY()-242),$M$7:$M$94)
View 9 Replies
View Related
Aug 31, 2012
I need to identify every 5th occurence of a product code (In column B)
the list won't be consistent i.e. Code 80100811 may appear in row 2, 17, 35, 47, 51
So I would need to identify the one in Row 51 in the above example,
WorksOrderNumber
Product Code
DateEntered
Column1
56
80100811
01/08/2012 00:00
[code]......
View 5 Replies
View Related
Jan 27, 2009
The following formula takes a census of people in our area by date and by hour (sample data attached): =SUMPRODUCT(--(FLOOR(Data!$B$4:$B$39,1/24)<=$B2+C$1)*(Data!$C$4:$C$39>=$B2+C$1))
The formula works great, but I would like to add another component whereby the census will only count someone if they have a certain description (see column A - with color descriptions). So how can I setup the formula, to say, only look at the "red" colors during the same specified dates/times and give me the same type of census...instead of looking at all of the data or creating 50 different ranges for all the colors?
View 2 Replies
View Related
Sep 29, 2009
I'm trying to do what I think its a simple formula with Excel 03 but can't get it to work. The info is in two work sheets - A&B.
[code]WORKSHEET A WORKSHEET B
A COLUMN 1 COLUMN 2 COLUMN 3 COLUMN1 COLUMN 2
B BLANK BLANK 10 BLANK DATE:1/28/09
C NAME DATE WORKED 12 NAME
D Jones 1/2/09 13 Bell
E Smith 1/6/09 12 Smith
F Cox 1/28/09 12 Cox
G Cox 1/28/09 10 Collins
H Collins 1/28/09 11 Jones
In worksheet B, Column 2, I need to pull the SUM of COLUMN 3 IF THE NAMES IN BOTH MATCH AND THE DATES MATCH
I've spent hours tyring to get this thing done!
View 11 Replies
View Related
Aug 31, 2012
I am stumped on coming up with a dynamic formula that calculates a value based on an increasing number of "sums of products". Specifically, I need to calculate the total number of repeat users based on a "decaying' repeat rate. In other words, I need it to calculate = Sum[... +(d5*f5)+(d4*g4)+(d3*h3)]
or in equation form:
where
n = (1, "infinity")
and
i = {1, 2, ... (n-1)}
with
"Xn" is the calculated value of repeat users for month "n" (output/result cell)
"mi" is the number of new users (in month i)
"Nn-i" is the column 'number' of the repeat rate value (%) multiplier
Here is a screenshot of the first couple rows to illustrate:
I actually have about 70 months of data (rows) and roughly the same amount of columns representing a decaying repeat rate (70 or so) and these will be increasing as time goes on so I need this formula to be dynamic (i.e. - copy it down the column of (# of repeat users)
I am using Excel for Mac 2011.
View 9 Replies
View Related
Jul 18, 2014
I currently have a formula that totals a product between two dates, however I need to add up a reason code for that product also in a different tab. My current formula is
=SUMPRODUCT(--('Product'!$A$2:$A$1069=B6), --('Product'!$B$2:$B$1069=Data!$A$1))
I want to add another section so that it looks at the dates between C6 & B6 and also the product in Data A1 but totals the reason from cell C10 in the data worksheet.
View 1 Replies
View Related
Jun 11, 2014
My problem is a little more complex than sumproduct.
I have a ratings column and exposure column. I want to weight those ratings before doing a sumproduct, at the same time ignoring #value.
In the attached file, I want to achieve the green cell in one go, based on the orange raw data.
Attached File : Weight Data.xlsx‎
View 2 Replies
View Related
Jan 5, 2014
How to solve my problem in attached file : Week Product Level Count.xlsx
View 1 Replies
View Related
