< Go Back

Sum if cells contain both x and y

Generic Formula

=SUMIFS(range1,range2,"*value1*",range2,"*value2*")

Explanation

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.

Example

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:

=SUMIFS(C6:C10,B6:B10,"*Sweden*",B6:B10,"*Brazil*")

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.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar