sumif vs sumifs syntax

[lezawang]

Hi
What is the reason behind changing the argument orders in sumif() and sumifs() functions

Sumif(range, criteria, sum_range)

Sumifs(sum_range, criteria range, criteria)

The inconsistency is so confusing. The same apply with countif and countifs. I am just curious that is the reason behind this?

Thank you.

 

[Michael M]

Microsoft Engineers.....I don't believe there would be any other reason apart from "because we can" !!!

 

[Jerry Sullivan]

I agree that it is confusing, however I think it's due to the historical development of the functions and the need to place the optional parameters at the end.

For Sumif, the sum_range parameter is optional. If it was the first parameter, then all 3 parameters would be required. At the time, the idea of allowing only two parameters instead of 3 for those scenarios where it applied, probably seems like a good idea.
Sumif(range, criteria, [sum_range])

For Sumifs, there are 3 required parameters. Then, you can optionally have many additional pairs of criteria range-criteria.
Sumifs(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],...)

Sumifs was added to Excel many years after Sumif. If they were designed at the same time, there might have been some benefit in making Sumif have 3 required parameters so that the two similar function had similar order:
Sumif(sum_range, criteria, criteria_range1)

However, if that were done, there's not much reason to have separate functions because you can use Sumifs with just 3 required parameters in lieu of Sumif.

Changing the orders of the parameters of Sumif when Sumif was released would have caused havoc with millions of existing workbooks.

 

[lezawang]

Thanks for your reply. It is true Sumif() third argument is optional but I do not see a use of Sumif() with 2 arguments only? Is there any use? Thanks

 

[Jerry Sullivan]

Thanks for your reply. It is true Sumif() third argument is optional but I do not see a use of Sumif() with 2 arguments only? Is there any use? Thanks

An example would be to sum all the numbers in Column A which are greater than 100.

 

[lezawang]

An example would be to sum all the numbers in Column A which are greater than 100.

Thanks. Oh my god, then Microsoft should have called the first argument as Sum_range because it is the range that you are going to sum it. Am I right?

 

[Aladin Akyurek]

I think we can consider the functions requiring just a single criteria range like SUMIF, COUNTIF, and AVERAGEIF as obsolete and switch in all cases to ones that require multiple criteria range: SUMIFS, COUNTIFS, and AVERAGEIFS. Note that Excel now also offers MAXIFS and MINIFS.

 

[Jerry Sullivan]

Thanks. Oh my god, then Microsoft should have called the first argument as Sum_range because it is the range that you are going to sum it. Am I right?

The first parameter of Sumif is always the Criteria range and sometimes also the Sum range.