Excel Question

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.

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.

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.

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!

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.

Perfect that's done the job! Thanks!

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