How to Remember SUMIF and SUMIFS Formulas

[bencar]

Hi guys,

I get confused as to the syntax between SUMIF and SUMIFS. Do you know a way you guys can remember each syntax without getting confused one with the other?

 

[thisoldman]

Using the data dave2018 posted, I can calculate the sum of column A where the entry in column B is "West" with either of these two formulas:

=SUMIFS(A2:A100, B2:B100, "West")
or
=SUMIF(B2:B100, "West", A2:A100)

Both formulas return 158.


Similarly, If I want to calculate the sum of A2:A100 where the values are greater than or equal to 10:

=SUMIFS(A2:A100, A2:A100, ">=10")
or
=SUMIF(A2:A100, ">=10")

Both formulas return 100.

 

[bencar]

"Replace this cell with a formula to sum the values in column A that are greater or equal to 5"

Hi dave,

I put in SUMIFS(A1:A99,"=5") following your sheet. I get an error. So I dont think SUMIFS work with a single criteria.

 

[bencar]

You can get along only knowing SUMIFS. It works for a single criteria range, =SUMIFS(A1:A10, B1:B10, TRUE).

Hi thisoldman,

I tried using, =SUMIFS(A1:A99,"=5") and =SUMIFS(A1:A99,"=5", TRUE) and both dont work on the sheet dave provided. As I said I dont think single criteria works for SUMIFS.

 

[bencar]

Hi thisoldman,

I tried using, =SUMIFS(A1:A99,"=5") and =SUMIFS(A1:A99,"=5", TRUE) and both dont work on the sheet dave provided. As I said I dont think single criteria works for SUMIFS.

Also forgot to mention.. could you explain what you meant by: =SUMIFS(A1:A10, B1:B10, TRUE) ?? I tried using 'TRUE' but it doesnt work in single criteria in SUMIFS.

 

[dave2018]

Hi dave,

I put in SUMIFS(A1:A99,"=5") following your sheet. I get an error. So I dont think SUMIFS work with a single criteria.

In sumifs, u need at least 3 arguments at any given time, even with 1 criteria. so 1)specify the sum range. 2)specify the criteria range (it may be the same as the sum range)
3)specify the criteria....

Hope this helps.

 

[thisoldman]

SUMIFS always takes threes arguments: =SUMIFS(range_to_sum, criteria_range, criteria)

To sum all the values equal to 5 in column A using SUMIFS, I would write:
=SUMIFS(A2:A100, A2:A100, 5)
No argument can be skipped when you use SUMIFS. The range_to_sum is the same as the criteria range but you must write it twice.

SUMIF has two forms: =SUMIF(range, criteria) and SUMIF(criteria_range, criteria, range_to_sum)
With the two-argument form, the range_to_sum must be the criteria_range.
When you use the three-argument form, the criteria_range and the range_to_sum may be different, but they may also be the same range.

To sum all the values equal to 5 in column A using SUMIF, you could write:
=SUMIF(A2:A100, 5)
Or you could write:
=SUMIF(A2:A100, 5, A2:A100)

I unintentionally misled you with 'TRUE' in my one formula. Dave's data wasn't yet posted and I simply wanted to write a generic criteria. I apologize for the confusion.

 

[Aladin Akyurek]

Hi guys,

I get confused as to the syntax between SUMIF and SUMIFS. Do you know a way you guys can remember each syntax without getting confused one with the other?

1.

=SUMIF(A2:A10,">0",A2:A10)

2.

=SUMIF(A2:A10,">0")

3.

=SUMIFS(A2:A10,A2:A10,">0")

[1] and [2] are identical in behavior; we can say that [2] is legal.

[3] is the SUMIFS version [1] (and of course of [2])

There is no shorter version of [3].

[3] proofs that a two argument SUMIFS is legal.

Note. The range to sum in these formulas is bolded.