Counting Products Sold Per Rep
Aug 27, 2008
I'm trying to write a formula to count under certain conditions. I tried the following (and some other variations), but it doesn't work, it comes back #VALUE!:
=COUNTIFS('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$10:$K$100,D7,'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$A:$A,A34)
I need to count the number of products a rep sold in one day. He can have several sales in one day, and also sell more than one product per sale. The list I am pulling from has all sales from all reps for the day.
View 10 Replies
ADVERTISEMENT
Feb 28, 2012
I have a table with sales teams down the left, and products along the top.
The table shows how many sales of each product a team has made. This is populated from a sales data dump each day.
I need a second table with all the products along the top, and beneath each product, a list of which Teams have sold that product.
do I need some Index / Match style formula here to pickup up each team from the left in the first table that's got a sales value higher than 0 for a particular product?
View 5 Replies
View Related
Jun 11, 2009
I have a 7000 row spreadsheet of items sold over the past 6 months. I sorted them by their SKU, but some of them sold in multiple qty in a given day.
What is a formula to calculate the total qty of each SKU sold?
Example:
Column A = SKU 001
Column B = Qty Sold in a particular purchase (most have 1, but some are 2 or 3 or 4, etc.)
Column C, (Row 10) = Total where I would like to come up with a number for columns 1-10 (for example).
next item:
Column A = SKU 002
Column B = Qty Sold in a particular purchase (most have 1, but some are 2 or 3 or 4, etc.)
Column C, (Row 17) = Total where I would like to come up with a number for columns 11-17 (for example).
View 8 Replies
View Related
Mar 27, 2014
Find attached , expected result in on w2 needs to be 9 that I have punched manually : Team(A)01.xls
View 14 Replies
View Related
May 8, 2008
I need a formula that will tell me what % of the first 5 units sold are new, based on the dates provided. This is an example of the data I am working with. What i am looking for is a cell that will respond with .4 (meaning 40% of the first 5 units are new) ...
View 9 Replies
View Related
Mar 9, 2009
I have a one year sheet for a customer. Column A has a 6 digit part number, Column B has the quantity of that part number and Column C has the date they of the invoice. There could be several rows with the same part number (say, if they purchase them monthly, or weekly they would have 12 or 52 rows, etc.). If I want to find out how many of part # 123456 have been sold all year, is there a formula I could use in Column D?
View 8 Replies
View Related
Oct 7, 2013
I have created a spreadsheet as follows:
Date revenue total tickets sold total tickets sold per day
10-1 166,453 15374
10-2 166,915 15414
How do I formulate so that the numbers automatically update per day
View 2 Replies
View Related
Dec 4, 2011
I have a table and I want to find out the what is max number for Apple sold on Monday.
FruitsQtyDaysApple20SaturdayApple50SundayApple100MondayApple500MondayApple20
MondayMango50MondayBanana640FridayMango30FridayGet MAX ForApples on Monday500
View 9 Replies
View Related
Jan 29, 2009
In table A i have the purchase date of all the articles bought with their corresponding quantities.
Table A:
Purches DateArticleQuantity12/12/2008Radio2012/12/2008Binocular6012/12/2008Fan3112/12/2008Iron5012/12/2008Sun Glass15012/12/2008Tv612/12/2008Head Phone3712/12/20082G Card12
In table B i have the sold dates of all the articles with their corresponding quantities.
Table B:
Sold On ArticleQuantity15/12/2008Iron115/12/20082G Card115/12/2008Radio216/12/2008Tv116/12/2008Iron217/01/2008Head Phone317/12/2008Iron117/12/20082G Card122/12/2008Radio127/12/2008Fan1
An finally table C is a result chart which shows the days between the purchase and the selling dates of the articles.
Table C:
Article Sale After purches date 12/12/2008 Days ?Sale RepeatRadioBinocularFanIronSun GlassTvHead Phone2G CardAfter Every ? Days3163453After Every ? Days1145After Every ? Days5After Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? Days
View 9 Replies
View Related
Apr 6, 2008
A worksheet called "purchases" contains the list of shares purchased during the period 1.4.5 to 31.3.8. It has four columns :
1. Date
2. Name of the share (name is repeated for every new purchase as it is entered in a new row and is totally independent of the earlier entry of purchase)
3. Qty
4. Sale price
Likewise there is a sheet for sales also. I want to do the following:
a) Find out all the records showing purchases of a particular share and add them up (both quantity and value). And thus, make a list of shares showing aggregate purchase of each share during the entire year. Likewise, I want to do this for sales also.
b) For every share that has been sold within one year after its purchase, apply 20% tax on the profit earned on sale of such share.
Additional information: If 100 shares of A Ltd are purchased on 1.4.5 and 200 shares on 1.5.5 then during the sale of 150 shares of A Ltd on 2.4.6, 100 shares purchased on 1.4.5 and 50 shares purchased on 1.5.5 shall be deemed to have been sold.
View 5 Replies
View Related
Nov 22, 2007
I can't seem to get my head around this one. I have a list of ranges of prices for different materials depending on the quantity bought. I want a formula that returns the correct price depending on the quantity required.
Please see attached workbook. My desired results are in column S. I can't stop thinking that a nested IF statement is the solution so I was hoping for a fresh pair of eyes on it.
View 7 Replies
View Related
Jul 15, 2014
We have 5 - six month programs and five - 12 month programs we are going to sell; each have a different price. I Want to insert number of projected sales manually for each month and then have excel distribute/spread revenue evenly over six or twelve months into a separate set of cells. First payment would be received at time of sale.
View 4 Replies
View Related
Jun 16, 2009
I have a very long Excel spreadsheet (>2500 rows by approx 20 columns) that consists of >400 customer statements. At the top of each invoice, in colun B, is the text "Service Requestor Contract Number", and 5 rows down is the respective contract number. Further down (and the number of rows differ for each contract as each has its own unique level of detail) , again in column B is the text "Current Month Charges", and on the same row, in columns K and O, the current month number of items sold and $ charges respectfully. The spreadsheet is NOT paginated for each respective statement.
What I need to do on a separate spreadsheet is sum, by contract, both the number of widgets sold, as well as the amounts due.
View 9 Replies
View Related
Jan 8, 2010
I am sure this is simple but I have not found out the solution anyway....
Example:
A B C D E F G
1 2 100 0 0 2 100 400
2 2 100 10 50 0 0 700
3 2 100 0 0 2 200 600
Columns A, C and E are number of items on each "unit".
Columns B, D and F are the number of "units" (e.g. to produce).
Column G are the resulting number of items that need to be purchased.
So, column G is a sum of products and the formula found below if put in G1 will do the trick. =A1*B1+C1*D1+E1*F1. What I would like instead is to have a "formula" that allows me to insert new pairs of columns without having to change the formula to include the new column pairs.
View 2 Replies
View Related
Jun 12, 2009
I have a list of parts (3000+) that have different discounts.
When I enter the part#, I want the result to be"Price w/Discount.
I can do a vlookup, and also the index and match even with ctrl+shift+enter. But the results only give me just the price. If possible,
I would like for excel to do this without adding an extra column for the discount. I'm stumped on this.
Example:
In column A2:A13 I have part numbers, in column B2:B13 I have prices.
In column D2, I have the lookup and the result to be placed in column E2.
Note: I have a discount table in J2:J5 which range from
.61, .62, .72 and.79.
View 9 Replies
View Related
Aug 11, 2009
Basically its a invoicing system. I tried with all the products in the same excel file but then I realized if i update the product list it wont in all excel files for each customer. So i thought one external access database (or excel file) that I could refer to from an excel file.
So what I want to do now is when I want to add a new product to an invoice, I click a button (add) and it opens a small window with the list of products which has been taken from say an access database or excel file. Then I select the product and click insert.
How would I go about doing this? Are there any simple example I could follow?
View 14 Replies
View Related
Mar 6, 2006
i have a payroll grid. it has 3 cells with drop downs that have 39 options.
there codes for payments. i need a fourth cell to show a total dollor amount based of the codes selected.
separate question. i have a list of cells with the same four options (Job Types.ie service call, new connect...) i want to limit the cells mentioned before so that payment codes that do not pertain to the job type can not be selected.
View 9 Replies
View Related
Mar 13, 2009
I'm not 100% sure how to describe what I need to do but here goes. I have two similar spread sheets. The first contains all the products in a shop with the new prices and new item codes.
The second sheet has all the old item codes old prices and some duplicates and items that do not exist any more. Both spread sheets have lots of column but only 3 that I need to worry about. Item code, Product Name and Price.
What I have tried is using the If command (in the item code column) to search through the Product Names (on the updated sheet) and put the right item code next to the right product (on the old sheet) but it didn't work.
View 5 Replies
View Related
Jun 17, 2009
I would like to create a new products numbers column in Excel.
I have my supplier "SKU's numbers" in column A , I would like to create my "SKU's numbers" in XXXX format (only numbers) in column B.
Could someone help me pls todo it with Macro or if it possible todo it in formula ?
The code should check in the excel sheet, that it is not generate a new SKU that was used before !
View 9 Replies
View Related
Jun 5, 2007
I have an Excel Sheet which has the Products column. I need to insert Category beside every products.
View 7 Replies
View Related
May 5, 2009
I am trying to identify new products that are not in my 'stocklist'. In the 'new prices' sheet I have =IF(ISNUMBER(MATCH(C4,'Stock List'!C:C,0)),"","NEW") dragged down in column G. Though its throwing out 'New' on every item which is incorrect as there are loads of items already in both sheets. This sounds confusing but if you look at the attached im sure you will see what im trying to do.
One last problem is that some of the new codes have zeros in front of them so the formula will have to discount the 1st couple of digits IF they happen to be zero. example; C5480 in stock list is exactly the same as C398 in new prices. But because there is a zero in front of the C398 then the formula does not recognise that the two are the same.
View 5 Replies
View Related
Aug 4, 2009
I have a table that has 3 columns:
Product name (one of 15 products I have)
Purchase Date (converted to excel datevalue).
Week number (a number between 1 and 52)
I'm trying to figure out how to sum the products purchased in a given week.
View 6 Replies
View Related
Nov 4, 2005
I'm trying to calculate periodically sales for new products, which have been in the market for max 6 monts. After that 6 months the sales of the product is not to be calculated. I have a huge amount of products, where this information should be calculated, so manually calculating is not an option. The products are in rows, and periods are in columns. As the data concerns several years data there is a problem, that some products have in some months zero sales, and in the next month again some sales. This messes up always my calculations. How to truly take only the first 6 months, and leave all the rest uncalculated?
View 9 Replies
View Related
Jan 20, 2007
I have multiple sheets in a workbook, and a want to sum the multiplication of two cells on each sheet on a total sheet. i.e. totalsheet!A1 = sheet1!A1 * sheet1!B2 + sheet2!A1* sheet2!B2....+ sheetN!A1*sheetN!B2
I need to do this for hundreds of cells, so mutiplying them on each sheet is not an option. There are enough sheets that writing out the brute force equation as above is too long. I know you can do total!A1=sum(sheet1..sheetN!A1) to add all sheet A1's together, but multiply and sum?
View 6 Replies
View Related
Mar 4, 2014
What I'm trying to do is show monthly performance based on products of a few different areas. Where I'm running into problems, is with the month listings. If the report shows one month at a time, I can get it to work with SUMIF formulas, however when there is more than one (and there will always be three).
View 6 Replies
View Related
May 1, 2013
I am doing a list which has the same products returning several times, but with different values. Want to filter/make a new list, with only one of each product and the summed amount of that specific product. Summing the specific amount is not that big of an issue, but the creation of the list is, least in a smart way i have tried this:
[Code]......
This being the last possible entry for the summed list.
My problem is that the formulas is getting too big for my computer :S, since this formula is copied more or less 10 times.
Here is an example of what i want: Product list.xlsx
View 5 Replies
View Related
May 11, 2013
Here is what i have so far: stock.xlsx
Basically, i have 2x base products but we can interchange the parts between the sets so we have 1 variant per base product.
But what i want to calculate is how many sets i can make out of the available units that are in stock.
View 12 Replies
View Related
Aug 4, 2014
So I have a spreadsheet of several thousand products (spare parts) and we received a folder of image files to go with these parts, however the file names aren't all uniform; some of the image files have the Part Number, while others use the SKU number. I have cells with the part # and SKU number for each product, and I could probably copy and paste the list of all image file names from the folder into the same spreadsheet. Is there a macro that could search the image file list for either the SKU or Part Number, and whichever is listed for each row, insert that file name into a cell? So I have this:
Item name SKU # Part #
Spare Part 1 123456 P0459381B
Spare Part 2 340934 P2394093A
etc
And then I have the list of image files in its own Sheet or whatever's easiest
P0459381B.jpg
340934.jpg
etc
So after the macro is complete, I'd have:
Item name SKU # Part # Image file
Spare Part 1 123456 P0459381B P0459381B.jpg
Spare Part 2 340934 P2394093A 340934.jpg
View 3 Replies
View Related
Jun 21, 2007
Try this on for size...
On the MSP sheet is a list of products with a weeknumber and quantity.
I need to combine products that match in the same week and add their quantities.
Then I need to put the quantities into the VOLUME worksheet.
Any products that arent listed need to be added on the bottom.
View 14 Replies
View Related
Apr 16, 2008
I want to produce a spreadsheet which automatically tidies up a transferral of data from a programme called sage in to a customer service spreadsheet for my company.
I am a bit of a novice but a keen one so please be patient! I do know what I want to achieve in the long run!
The problem I have at the moment is recognising products in one column and getting it to return the value in or out of stock in another. So far I have got it to recognise one product and return the correct statement (using the "IF" function) but i need it to do it for the whole column and for a range of products.
View 13 Replies
View Related
