I have a column that lists names, and I need to count how many there are for each name, sounds easy enough use the following i think.
=SUMPRODUCT((A1:A10="Joe")
The trick is, I only want it to sum them if it's within the current month. I am entering something wrong because I get an error message.
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”.
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.
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 Relatedformula that can pull the latest date for a product, if there are multiple dates attached to the product?
For example, in the attached, I have a Customer and Deal Date list, for Customer A, there are 3 dates, but I only want the latest date: 2/14/2014.
I want to return a "total" result for all the cells which contain a date within the last 12 months from todays date.
View 9 Replies View RelatedI 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?
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)
All is inside this file: [URL] ..........
View 9 Replies View RelatedI have another problem with my previous file that I need to finish. Because I want to make it clear I'll attach the files and pictures before the middle, and what can be the end of this table.
View 9 Replies View Relatedi 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.
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 Range("A1:F50").select
If Len(Cells.value) > 40 Then
activecell.clearcontents
End If
If I have column A with a department name e.g. A, B, C, etc
and
column B with a date next to the some of the entries of column A.
How would I compose a formula to return an answer of the amount of records that show column A as "A" and column B as "1".
This is probably immensely simple and I have a wet fish on standby to hit myself with.
I created a spreadsheet to calculate property "Return on Investments" (ROI). I then added a column with the If command. Here is an example formula:
=IF(AND(G12>=G4; G12>=G5; G12>=G6; G12>=G7; G12>=G8; G12>=G9; G12>=G10; G12>=G11; G12>=G13; G12>=G14; G12>=G15; G12>=G16; G12>=G17; G12>=G18; G12>=G19; G12>=G20; G12>=G21; G12>=G22; G12>=G23; G12>=G24; G12>=G25; G12>=G26; G12>=G27; G12>=G28);"Buy!!";"--")
I dont know if it matters but the ROI is a very small decimal percent. However I found that when 2 properties get close the "Buy!" symbol dissapears from all cells. I have compared all the formulas and found no issues.
I have attached the file to this post
The issue occurs when 1750 is but in cell B12. I have narrowed the issue to cells A6 and A10.
=IF(AND(G6>=G4; G6>=G5; G6>=G7; G6>=G8; G6>=G9; G6>=G10; G6>=G11; G6>=G12; G6>=G13; G6>=G14; G6>=G15; G6>=G16; G6>=G17; G6>=G18; G6>=G19; G6>=G20; G6>=G21; G6>=G22; G6>=G23; G6>=G24; G6>=G25; G6>=G26; G6>=G27; G6>=G28); "Buy!!"; "--")..............
I am trying to nest an ISERROR funtion into an IF AND statement.
Column B D E F
Cases Case Pack Reg Price VPR Price
1 14 2.25 0.00
1 14 1.75 2.00
When the value of F is zero, I want the formula to be B1*D1*E1, when the value of F is not zero, I want the formula to be F1*D1*B1. I can figure this part out. The problem is I have these values on a certain number of rows and I want a grand total at the bottom, but the rows that do not have values in them are giving me a #VALUE! error, which is messing up the grand total. I am not sure how to write the formula with the IF AND and ISERROR functions.
Chart updates correctly when new pricees added.
But my IF function in column C leaves the annoying line to zero until prices are enter in column B.
I am looking for the exact code to make a "SUMIF" function so that I can modify it to add another search criteria, such that I only wan the sum if it finds both in the range.
View 9 Replies View RelatedIs there a function to do this or only VB code?
View 14 Replies View Relatedhow to return value from VBA User Defined Funtion
View 9 Replies View RelatedI have several worksheets where I input data, and I would like a 'stats' page as worksheet 1. Work sheet one is a list of names in cells A4:A28. column B,C,D,E,F, and G contain the results using Countif. How would I now get excel to look down a column, for example B, to find the highest number in that column and then use the name from that line but in column A.
View 3 Replies View RelatedI have the following formula: ...................
and excel is saying I used too many characters and to use the concatenate function to do so, but I have no idea how.
I wanted to return text rather than a '0' in the case that all the original IF statements were false, I threw another IF statement in there and said if the original is equal to '0', then return "-". If not, then run through the original again (I pasted the original again after the "-").
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 RelatedI want to calulate a cell to show if a a product is in warranty or out of warranty from a date in another cell.
=IF (D25<365, "In Warranty","Non Warranty")
I think I may have got the syntax wrong.
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.
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)
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 Relateduses CSV files for importing product data, problem is both have it formatted differently. What i ideally need is a master file that i can change whenever a price needs changing or product adding, this feeds down into 2 csv files that are formatted for the relevent softwares and then i can use a macro to upload these automaticly at end of day. ive tried using just excel but had no luck, i tried using access and custom reports but keeping getting errors as page size is 35 fields long and doesnt want to export as a csv,
I have Workbook A and Workbook B(csv file)
In workbook one I want to look for a unique code that is in both workbooks and if it is then return a result of the total.
As an example
WORKBOOK A
a0YC000000YWPE1MAP
WORKBOOK B
Code qty
a0YC000000YWPE1MAP 10
a0YC000000YWPE1MAP 1
a0YC000000YWPE1MAP 14
Result I need is 25.
What to type if it is a different workbook and not just a sheet.
PRODUCT shows a value even if 0*# =IF(H15*I15=0,"",PRODUCT(H15,I15))
View 5 Replies View Related