Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


  Print Thread
Standard User gunnersboy
(fountain of knowledge) Thu 12-Sep-13 13:21:57
Print Post

Excel Question


[link to this post]
 
Hi

Hopefully someone with some good Excel knowledge will be able to help me out. I'm looking to create a formula which will provide up to date info based on ongoing information being added to the spreadsheet. The example is as follows. Let's say you have a column which is filled with values of pounds, £100, £150 etc in column A3:A300. In the column next to this are initials from staff that they input when they've saved the company the corresponding sum from column A. Now, I would like to create a formula which tells me in a different cell from either of the above how much each person has saved the company based on the formula finding their initials and then looking to the money value in the column beside this. Hopefully that's explained properly! Any ideas? I'm thinking a vlookup but I don't have the knowledge to make it all work...

Thanks in advance.

Virgin Media:120Mb/s

Download Speed: 123.8 Mb/s
Upload Speed: 10.0 Mb/s
My Broadband Speed Test
Standard User ian72
(knowledge is power) Thu 12-Sep-13 13:28:51
Print Post

Re: Excel Question


[re: gunnersboy] [link to this post]
 
Rather than a formula create a pivot table. If you select the data set and insert a pivot then select the column of initials as the row and the amounts as the sum. It will then do all the pesky calculations for you. Takes about 30 seconds once you've worked out how to do it.
Standard User ian72
(knowledge is power) Thu 12-Sep-13 13:32:19
Print Post

Re: Excel Question


[re: ian72] [link to this post]
 
If you don't want to use a pivot then look at SUMIF.

=SUMIF(A1..B7,"AA",B1..B7)

Initials in column A, amounts in column B. It will then use the second value ("AA" in this case) as the thing to search for and will use the values in B1 to B7 as the calculation. You can even replace the "AA" with a cell reference where you will store the initials to lookup.

Pivottable is better though.


Register (or login) on our website and you will not see this ad.

Standard User gunnersboy
(fountain of knowledge) Thu 12-Sep-13 15:08:15
Print Post

Re: Excel Question


[re: ian72] [link to this post]
 
Perfect thanks. I've used the second option as I've never used a pivot table before. Just a quick question. It works fine if the data is coming from the same sheet. However, each month has a different sheet and therefore at the end of September another sheet will be used whilst also wishing to retain the previous month. When I try and select the same cells for the October sheet it doesn't seem to like it. Comes up with #value. Can I just check should I be putting a : in between the two different sheets when selecting the data for the initials? Thanks again!

Virgin Media:120Mb/s

Download Speed: 123.8 Mb/s
Upload Speed: 10.0 Mb/s
My Broadband Speed Test
Standard User ian72
(knowledge is power) Thu 12-Sep-13 15:35:02
Print Post

Re: Excel Question


[re: gunnersboy] [link to this post]
 
Hmm, not 100% sure on that one.

However, you could have a single cell which does:

=SUMIF(Sheet1!A2:B6,"AA",Sheet1!B2:B6)+SUMIF(Sheet2!A2:B6,"AA",Sheet2!B2:B6)

With the SUMIF formula repeated for each sheet.
Standard User gunnersboy
(fountain of knowledge) Thu 12-Sep-13 19:17:03
Print Post

Re: Excel Question


[re: ian72] [link to this post]
 
Perfect that's done the job! Thanks!

Virgin Media:120Mb/s

Download Speed: 123.8 Mb/s
Upload Speed: 10.0 Mb/s
My Broadband Speed Test
Standard User ian72
(knowledge is power) Fri 13-Sep-13 09:24:45
Print Post

Re: Excel Question


[re: gunnersboy] [link to this post]
 
Glad it's working - worth having a look at using the pivot table wizard though if you get 5 minutes as it could actually result in a much simpler and more elegant solution wink
  Print Thread

Jump to