Using one cell to refer to manyClosed

Question

Hello, I'm trying to do something I'm not sure is possible. I have a spreadsheet to keep track of receipts and group items by category. In one column I have the date, in the next category, item, cost, etc. I want to gather data on the cost per month of each category. However, I only want to put the date in the first row for each receipt, because it's more visually clear than copying it the whole way down. So here's an example:

A1 - "1/25/15" B1 - "Groceries"
A2 - (Blank) B2 - "Hardware"
A3 - (Blank) B3 - "Groceries"
A4 - "2/2/15" B4 "Groceries"

Is there a way to take values in say column C from rows where the value of B is "Groceries" while in column A the next date even with or above the item is in January? (I've found a tool on another site for extracting the date name from a numerical month.)

Any help would be greatly appreciated!

RayH · Answer

I would suggested leaving the date in per row as this makes thing far, far easier to analyze data.  

Once all the data is in place then it can be reported on in anyway you want for reviewing numbers etc.

The best way to show what you need is by the use of Pivot Tables where data can grouped by fields you choose and totals applied to the groups.
They only need to be refreshed rather than recreated each time you need an update.

They are also not as scary as people think.

https://www.excel-easy.com/data-analysis/pivot-tables.html

Underwater · Answer

I decided to just make separate sheets for each month, so I can keep it the way I want and still analyze it by month... Thanks for replying!

Using one cell to refer to many

2 replies

Subscribe To Our Newsletter!