SUMIF with two conditions

the syntax for sumifs is different than what you are using. You can always get the syntax using the built-in function browser. type the first part of the function:

, then select the contextual menu using the down-pointing expose triangle:

now you can see the function browser on the right.

from that the help says:

SUMIFS(sum-values, test-values, condition, test-values…, condition…)

sum-values: A collection containing the values to be summed. sum-values can contain number values, date/time values, or duration values.

test-values: A collection containing values to be tested. test-values can contain any value.

condition: An expression that compares or tests values and results in the boolean value TRUE or FALSE. Condition can include comparison operators, constants, the ampersand concatenation operator, references, and wildcards. You can use wildcards to match any single character or multiple characters in the expression. You can use a ? (question mark) to represent one character, an * (asterisk) to represent multiple characters, and a ~ (tilde) to specify that the following character should be matched rather than used as a wildcard.

test-values…: Optionally include one or more additional collections containing values to be tested. Each test-valuescollection must be followed immediately by a condition expression. This pattern of test-values, condition can be repeated as many times as needed.

condition…: If an optional collection of test-values is included, an additional expression that results in a boolean value TRUE or FALSE. There must be one condition following each test-valuescollection; therefore, this function will always have an odd number of arguments.

so the syntax is one range to sum followed by pairs of a test value range and a condition

I made a "short version" of you formula that only summed a few cells of a table

The correct version for you is:

=SUMIFS(C2:C3169;C2:C3169;"<500";C2:C3169;">250")

note in the US we use the comma as the thousands separator and the period for the decimal. So we use the comma as the argument delimiter. However where you are you use the commas as the decimal so you use the semicolon as the argument delimiter.

Hi suntrop,

Just a note to add:

In your example, you specify the first range of values to be summed as "<250" and the second range as ">250 and <500". With those range settings, any cell containing the exact value 250 will be excluded from both sums.

To include 250 (and to include 500 in the second range), change "<250" to "<=250" in the first range condition, and make the same change for the highest value condition for each of the following ranges.

OR

To include 250 in the second range (and 500 in the third, etc.), leave the "<" signs as is and change the ">" signs to ">=" for the lowest value to be included in each range.

Example:

Formula shown below the table is from cell D2.

Formulas in cells below D2 contain the edited conditions shown in column C.

Regards,

Barry