Excel find word in series of cells and add

Payroll - Aug 31, 2009 at 10:44 AM
 matt - Mar 17, 2017 at 11:46 PM

My colleague wants to has a spreadsheet with many columns and rows. The spreadsheet setup is something like this:

Cells under column A have words in them. Each cell under this column will have one of the months of the year in it, as well as other text with the month of the year. So it could be "January Sales Log" or "January Bonus Spreadsheet" or "April Expenses Information."

Cells under column B have dollar values in it. They are the dollars spend for the item in the same row in column A. So B1 would have the dollar amount associated with whatever A1 is.

She wants to put a formula in a separate cell of another column. This formula should find the cells that contain "January" and then add the values associated for all cells associated with "January." So if A1 is "January Bonus Spreadsheet" and A4 is "January Sales Log", the new cell D15 would add the values in cell B1 and B4, which are both associated with amounts spent in the month of January.

This totals cell (D15 for this example) would not include in its sum any totals from cells under column B that did not have the specific month in the related cell for column A.

D15 would be for Jan. totals.
D216 would be for Feb. totals. Etc.

Can this be done?

2 replies

aquarelle Posts 7118 Registration date Saturday April 7, 2007 Status Moderator Last seen July 29, 2022 491
Sep 2, 2009 at 05:20 PM

According to your example, the formula in D15, to add the values of all cells in column B which correspond to all the cells in column A containing the word "January" is :

in D216, you just have to change "January" by "February" :

Do the same to the other months.

Best regards
thank you so much!!!!!!! (i'd use caps but it doesn't let me)
How would you accomplish this is you want to combine the totals of multiple spreadsheets formatted the same way? an example would be different spreadsheets within the same workbook corresponding to different annual quarters?