Tally of prices in Excel depending on an adjacent column?
February 12, 2018 9:54 AM Subscribe
I'm dividing up tickets between 4 people and I need help creating a tally of those prices to see how much everyone will owe.
I've a column of prices (X) and I've a column of the names (Y) and I don't know how to create such that every time Alice's name appears in Y, the price for that event in X gets added to a ticker elsewhere in the sheet, and every time Bob's name appears in Y the right price gets added to his tally in X. I would think this should be simple enough but I'm at a basic level with Excel and don't know how to do it.
For what it's worth, I'm using Excel for Mac, version 15.32 if that matters. Any help in figuring this out would be great!
I've a column of prices (X) and I've a column of the names (Y) and I don't know how to create such that every time Alice's name appears in Y, the price for that event in X gets added to a ticker elsewhere in the sheet, and every time Bob's name appears in Y the right price gets added to his tally in X. I would think this should be simple enough but I'm at a basic level with Excel and don't know how to do it.
For what it's worth, I'm using Excel for Mac, version 15.32 if that matters. Any help in figuring this out would be great!
Best answer: You can do this with a Pivot Table
Or you can do this using SUMIFS. I'm happy to detail this solution, although I do think the Pivot Table is the better way to go.
Google gave me this introduction to pivot tables. I'm also happy to answer any questions you may have.
posted by teabag at 9:58 AM on February 12, 2018 [2 favorites]
Or you can do this using SUMIFS. I'm happy to detail this solution, although I do think the Pivot Table is the better way to go.
Google gave me this introduction to pivot tables. I'm also happy to answer any questions you may have.
posted by teabag at 9:58 AM on February 12, 2018 [2 favorites]
Best answer: If it's just two columns (price and name) a SUMIF function will suffice, as opposed to a SUMIFS which is necessary when you have multiple criteria.
On preview: pivot tables are stronger, but SUMIF/SUMIFS formulas have the advantage that they automatically update as you add new information.
Pivot tables on the other hand need to be manually refreshed unless you set them to automatically refresh upon opening the document (which is still less frequent than adding new data) or via VBA programming, which is probably not realistic given the Excel levels implied in this question.
posted by andrewesque at 10:01 AM on February 12, 2018 [1 favorite]
On preview: pivot tables are stronger, but SUMIF/SUMIFS formulas have the advantage that they automatically update as you add new information.
Pivot tables on the other hand need to be manually refreshed unless you set them to automatically refresh upon opening the document (which is still less frequent than adding new data) or via VBA programming, which is probably not realistic given the Excel levels implied in this question.
posted by andrewesque at 10:01 AM on February 12, 2018 [1 favorite]
Response by poster: Thank you all, I currently need it to update live, so will use Sumif. And you're right andrewesque, I'm definitely at the lower end of excel skill :).
Appreciate the help!
posted by Carillon at 10:24 AM on February 12, 2018
Appreciate the help!
posted by Carillon at 10:24 AM on February 12, 2018
This thread is closed to new comments.
posted by paper chromatographologist at 9:56 AM on February 12, 2018