Go Back

Sum if equal to one of many things

Excel allows a user to sum values if a cell is equal to one of many values from the other range, using the SUMPRODUCT and SUMIF functions. This step by step tutorial will assist all levels of Excel users in summing values if a cell is equal to one of many things.

Figure 1. The result of the formula

Syntax of the SUMIF Formula

The generic formula for the SUMIF function is:

=SUMIF(range, criteria_range, [sum_range])

The parameters of the SUMIF function are:

  • range – a range where we want to apply our criteria
  • criteria – a criteria range where we want to find a value from a range
  • [sum_range] – a range which we want to sum. This is an optional parameter. If it’s omitted, the range will be summed.

Syntax of the SUMPRODUCT Formula

=SUMPRODUCT(array)

The parameter of the SUMPRODUCT function is:

  • array – an array of values which we want to sum.

Setting up Our Data for the Formula

Let’s look at the structure of the data we will use. Our table consists of 3 columns: “Delivery Number” (column B), “Month” (column C) and “Amount” (column D). The criteria range is F3:F4 (“Months”). The result will be in the cell H3.

Figure 2. Data that we will use in the example

Sum Amounts if a Cell is Equal to One of Many Things

In our example, we want to sum all amounts which corresponding month is Oct or Sep 2018. The result is in the cell H3.

The formula looks like:

=SUMPRODUCT(SUMIF(C3:C11, F3:F4, D3:D11))

The range of the SUMIF function is the C3:C11 range, while the criteria is F3:F4. The sum_range parameter is D3:D11. The result of the function is the array parameter of the SUMPRODUCT function. Finally, the function sums all these values.

To apply the formula, we need to follow these steps:

  • Select cell H3 and click on it
  • Insert the formula: =SUMPRODUCT(SUMIF(C3:C11, F3:F4, D3:D11))
  • Press enter

Figure 3. Using the formula to sum if a cell is equal to one of many things

As we can see in Figure 3, 4 rows meet the criteria of the SUMIF function. Rows 3 and 6 have Oct 2018 and rows 4 and 8 have Sep 2018. Therefore, the result in H3 is the sum of corresponding amounts ($1,200, $990, $250 and $650) – $3,090.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hi there, I am designing a macro for below macro but unable to identify the macro logic please help 1. If Sum Of the Hardware(Range A25,A32) is not equal to 0 then the channel rebate(P32..P54) = 18% 2. If any of the training ordered by customer(Range B32..B45)(any one range not equal to 0) + Sum of hardware(A25,A32) is not equal to 0 then rebate(P32..P54) = 21% 3. If Material pack ordered by customer (Range B65..74) (any one range not equal to 0) + If any of the training ordered by customer(Range B32..B45)(any one range not equal to 0) + Sum of hardware(A25,A32) is not equal to 0 then rebate(P32..P54) = 24% Your help would be really appreciated!
Solved by E. F. in 12 mins
I need to say whether the sum of a column if equal or over $2000
Solved by V. U. in 26 mins
If cells A4 and A5 are equal then perform =SUM(D5+H4) If cells A4 and A5 are not equal then perform =SUM(D5+G5)
Solved by V. U. in 18 mins

Leave a Comment

avatar