Report

Excel find word in series of cells and add [Solved/Closed]

Ask a question Payroll - Last answered on Mar 17, 2017 at 11:46 PM by matt
Hello,

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?
See more 
Helpful
+7
plus moins
Hi,

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 :
=SUMIF(A:A,"*January*",B:B)

in D216, you just have to change "January" by "February" :
=SUMIF(A:A,"*February*",B:B)

Do the same to the other months.

Best regards
Was this answer helpful?  
matt- Mar 17, 2017 at 11:46 PM
thank you so much!!!!!!! (i'd use caps but it doesn't let me)
Reply
Helpful
+0
plus moins
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?

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!