Search code examples
excelspreadsheetfinance

Formula in which I look for a specific word in column C(that exists in multiple cells), take the corresponding amount in column D,then sum the amounts

I've recently started a excel spreadsheet to track my spending. I've been trying to find a way to group certain expenses together to see my monthly spending in those areas. Maybe there's an easier way but I was trying an =IF formula to find a word such as "gas", then take the cell to the right(where the amount would be) and sum all of the gas expenses in row 1. The same with "food" or Misc".

Example of Spreadsheet:
Example of Spreadsheet

Solution

  • If you want to display it alongside the input data (so not in a pivot), you can use the SUMIF formula.

    SUMIF or SUMIFS are as simple as defining the range you want to SUM, the criteria you want to match and the range you want to look in.

    So in this instance (based on my screenshot below), in Cell D2 you would add the following formula and fill it across to Cell F2:

    =SUMIF($A:$A,D$1,$B:$B)

    Example:

    enter image description here

    Note: The values in the yellow highlighted cells (headings) must EXACTLY match the spelling you have in column A (item).