The SUMIFS function can be perfectly used to sum cell that contains both X and Y values (i.e. contains “Sweden” and “Brazil”) in the same cell.
The SUMIFS function used the ‘AND’ (addition) logic to function automatically. All you need do is just to provide two range/criteria pairs. Make sure that both are within the same range. For example, the two ranges are (B6:B10, C6:C10).
- While for the criteria, both cells must contain “Sweden” and also contain “Brazil”. We then use an asterisk, a wildcard that matches “one or more characters” together. Make sure the asterisk is put both in front and behinds to allow the formula to match “Sweden” and “Brazil” wherever they appear in a particular cell.
- Once the two criteria return TRUE within the same row/cell, SUMIFS then find the sum of these value and return the result to column C.
Figure 1: Example showing how to use the excel sum if cells contain both X and Y to calculate the sum of values of cells containing both Sweden and Brazil
The formula used in F7 is:
This returns 220 as the sum of the two cells containing Sweden and Brazil.
Point to note about the Excel Sum if cells contain both X and Y
- The Excel SUMIFs function is not case-sensitive, rather it is character sensitive.