Mr Excel & Excelisfun Trick43: End Inventory Value From Transactional Records SUMPRODUCT SUMIF

Just This Video For Fun

http://www.youtube.com/watch?v=0VvjrldlqI0

I'm looking for a good way to capture repair records for each piece in our inventory. I know that's pretty generic but I'm starting from scratch so I'm wide open to ideas. What I imagine is having an inventory sheet like the one attached here... but with the ability to click a (+) button beside each record so it branches down and shows all the repair records. Using that info I could collect which brand or equipment has been serviced the most, the least, number of times, etc. Something where I could add comments and other info. Does anyone have a good idea?

Thanks,
Joe

hi. my company wants to make a simple spreadsheet to record some stock items and how much we use.

they are packs of numbers. each pack has 7-1's, 2-2's, 1-3, 2-4's, 2-5's, 2-6's, 1-7, 1-8, 1-9 and 1-0.

i want to be able to press a button every time we use a pack so it records that we have used it and subtracts the amount used from the original stock amount. then at the end of each week i want to press another button to take the weekly amount and record that.

i know what i want it to do i just dont know the coding to make it do it. i will try and explain what i want. when i press the button it takes 7 away from the 1's, 2 away from the 2's, 1 away from the 3's etc etc. then records all this on another sheet moving down a line everytime i press the button. then at the end of the week i press another button and it records the currect stock levels (after the subtractions of all the weekly numbers) onto another sheet it then deletes all the weekly stock use and gives me a blank sheet for the following week. then the same again the next week, at the end of the second week it moves the last weeks stock down one line and puts in the total of the current week.

anyone can give me a hand?

thanks

please help

in summary column A contains an indentifier [2 or 3], column B contains a hourly rate, columns C to AC contain hours.

I'm trying to use a combination of Sumif and Sumproduct to get 2 totals for columns C to AC [1 total for indentifier 2 and 1 for indentified 3]

I'm trying to use

=Sumif(A7:A250,2,Sumproduct(B7:B250,C7:C250))


Dave

Hi All

I have a dataset which I need to change to transactional data. The data set has the following rows.

Account_No Region Account_Length Area_Code Phone UK_Package Broadband_Package Monthly_Broadband_Allowance Monthly_Broadband_Download Broadband_Balance Day_Mins Day_Calls Day_Charge Eve_Mins Eve_Calls Eve_Charge UK_Mins UK_Calls UK_Charge CustService_Calls Age Gender Churned 98352621 Portlaoise 142 57 416-8428 no yes 30 35 -5 84.8 95 14.42 136.7 63 11.62 14.2 6 3.83 2 18-35 female no 98352622 Portlaoise 65 57 416-8429 no no 0 0 0 129.1 137 21.95 228.5 83 19.42 12.7 6 3.43 6 18-35 male yes 98352623 Portlaoise 128 57 416-8430 no yes 50 45 5 265.1 110 45.07 197.4 99 16.78 10 3 2.7 1 55-70 female no

I need to change it to look like this

Account_No Variable 98352621 Portlaoise 98352621 142 98352621 57 98352621 416-8428 98352621 no 98352621 yes 98352621 30 98352621 35 98352621 -5 98352621 84.8 98352621 95 98352621 14.42 98352621 136.7 98352621 63 98352621 11.62 98352621 14.2 98352621 6 98352621 3.83 98352621 2 98352621 18-35 98352621 female 98352621 no 98352622 Portlaoise 98352622 65 98352622 57 98352622 416-8429 98352622 no 98352622 no 98352622 0 98352622 0 98352622 0 98352622 129.1 98352622 137 98352622 21.95 98352622 228.5 98352622 83 98352622 19.42 98352622 12.7 98352622 6 98352622 3.43 98352622 6 98352622 18-35 98352622 male 98352622 yes

Any help on this would be much appreciated

Regards

Donal

Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.

What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).

I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?

Thanks,

Peter Fraga
(fragapete@hotmail.com)

I am having trouble creating a similar spreadsheet to Excelisfun video #712. The only difference between mine and his, is that I have 3 blanks between the data sets. Can someone help?

http://www.youtube.com/user/ExcelIsF...22/Po35KC2ODNw

Hi All,

I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw

Dynamic sub tables based on a master sheet array formula

The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))

B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.

What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.

I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.

My question is...does anyone have any hints how to optimize this formula?

Thanks
Tony

Let's say I have three columns of data.

In Column A, is gender: male/female
In Column B, is name: Bob, Jon, Mary, etc
In Column C, is salary: $40, $30, $20, etc.

In column B, there will be instances where a name might appear more than once. I want to be able to do a sumif formula of Column A and Column C, but not include any records where a name in column B is listed more than once. Is there a way to do this?


1 Male Bob $50
2 Female Jane $30
3 Female Kate $20
4 Male Mike $30
5 Male Bob $50

So if I wanted to sum all male salaries above, it would be $80, not $130.

Hi Guys,

I have a large spreadsheet of usage, which I need to total a value for each user.

Basically - Column B contains a separate row for each use, with each row containing a value in Column H.

Is someway that I can quick obtain a list that shows the total of Column H, for each unique record in column B?

i.e. (sorry - at work and don't have Excel Jeanie installed):

Columnn B Column H User 1 20 User 1 40 User 1 30 User 2 498 User 3 75 User 3 78 User 3 58 User 3 97 User 3 1 User 4 2 User 4 9

With the end result being:
User 1 90 User 2 498 User 3 309 User 4 11
Thanks for any help you can give here guys