SUMIFS help page online?

[JoeU2004]

I don't have Excel 2007, so I cannot use offline help to learn about SUMIFS.

I've tried Google searches, but I have not found a canonical source of
online help for Excel SUMIFS per se.

I do find a tech ref page for the WorksheetFunction.SUMIFS method. That
should be the same. But that description is ambiguous; it does not answer
my question (below).

=SUMIFS(C1:C5,A1:A5,"x",B1:B5,"y")
The SUMPRODUCT equivalent:
=SUMPRODUCT(--(A1:A5="x"),--(B1:B5="y"),C1:C5)

That is the form of SUMIFS that I am familiar with: criteria are pairs
(range and selection criteria) after the first argument, which is the range
to summed conditionally.

In that form, SUMIFS would be limited to 14 conditions -- 29 arguments.

Not that that shouldn't be enough(!).

But the function does support 30 arguments. So I wonder if the following
single-argument criteria forms are also permitted (and useful to the extent
that they make sense logically):


1. Is SUMIFS(A:A,"<10") permissible?

In other words, sum cells in A:A if the cell is less than 10. Or must that
be written SUMIFS(A:A,A:A,"<10")?


2. Is SUMIFS(A:A,">0","<10") permissible?

In other words, sum cells A:A if the cell is greater than 0 and less than
10. Or must that be written SUMIFS(A:A,A:A,">0",A:A,"<10")?

 

[Ron Rosenfeld]

I don't have Excel 2007, so I cannot use offline help to learn about SUMIFS.

I've tried Google searches, but I have not found a canonical source of
online help for Excel SUMIFS per se.

I do find a tech ref page for the WorksheetFunction.SUMIFS method. That
should be the same. But that description is ambiguous; it does not answer
my question (below).



That is the form of SUMIFS that I am familiar with: criteria are pairs
(range and selection criteria) after the first argument, which is the range
to summed conditionally.

In that form, SUMIFS would be limited to 14 conditions -- 29 arguments.

Not that that shouldn't be enough(!).

But the function does support 30 arguments. So I wonder if the following
single-argument criteria forms are also permitted (and useful to the extent
that they make sense logically):


1. Is SUMIFS(A:A,"<10") permissible?

In other words, sum cells in A:A if the cell is less than 10. Or must that
be written SUMIFS(A:A,A:A,"<10")?


2. Is SUMIFS(A:A,">0","<10") permissible?

In other words, sum cells A:A if the cell is greater than 0 and less than
10. Or must that be written SUMIFS(A:A,A:A,">0",A:A,"<10")?

Joe,

I have not tested it, but Excel 2007 has a specification of 255 "arguments in a
function". Up to 127 range/criteria pairs are allowed.

Here is part of the description from HELP:

========================
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,
criteria2], …)

The SUMIFS function syntax has the following arguments (argument: A value that
provides information to an action, an event, a method, a property, a function,
or a procedure.):

sum_range Required. One or more cells to sum, including numbers or names,
ranges, or cell references (cell reference: The set of coordinates that a cell
occupies on a worksheet. For example, the reference of the cell that appears at
the intersection of column B and row 3 is B3.) that contain numbers. Blank and
text values are ignored.

criteria_range1 Required. The first range in which to evaluate the associated
criteria.

criteria1 Required. The criteria in the form of a number, expression, cell
reference, or text that define which cells in the criteria_range1 argument will
be added. For example, criteria can be expressed as 32, ">32", B4, "apples", or
"32."

criteria_range2, criteria2, … Optional. Additional ranges and their associated
criteria. Up to 127 range/criteria pairs are allowed.
======================================
--ron

 

[JoeU2004]

Here is part of the description from HELP:
[....]
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,
criteria2], .)

Excellent! This is much clearer then the WorksheetFunction.SUMIFS method
description that I stumbled across online at
http://msdn.microsoft.com/en-us/library/bb239775.aspx.

Thanks.

Based on that, I believe the answers to my questions are: we must do
SUMIFS(A:A,A:A,"<10") and SUMIFS(A:A,A:A,">0",A:A,"<10"). Please correct me
if I'm wrong.

Excel 2007 has a specification of 255 "arguments in a
function". Up to 127 range/criteria pairs are allowed.

Interesting. Does that mean the WorksheetFunction.SUMIFS method description
is incorrect? Or does that mean that WorksheetFunction.SUMIFS method is
more limited than the Excel function?

Say what?! I always thought that the WorksheetFunction methods are just a
way to access Excel functions. I thought the syntax and behavior are
identical because, in fact, we are executing the same function that Excel
uses, not look-alike work-alike code ("separate but equal" ;-).

Please tell me that assumption is correct. If it is not -- if the
WorksheetFunction method implementation is physically distinct from the
Excel implementation -- that opens a Pandora's box of potential trouble, no
matter what good might exist to keep the two implementations in sync when
changes are made.


----- original message -----

I don't have Excel 2007, so I cannot use offline help to learn about
SUMIFS.

I've tried Google searches, but I have not found a canonical source of
online help for Excel SUMIFS per se.

I do find a tech ref page for the WorksheetFunction.SUMIFS method. That
should be the same. But that description is ambiguous; it does not answer
my question (below).



That is the fom of SUMIFS that I am familiar with: criteria are pairs
(range and selection criteria) after the first argument, which is the
range
to summed conditionally.

In that form, SUMIFS would be limited to 14 conditions -- 29 arguments.

Not that that shouldn't be enough(!).

But the function does support 30 arguments. So I wonder if the following
single-argument criteria forms are also permitted (and useful to the
extent
that they make sense logically):


1. Is SUMIFS(A:A,"<10") permissible?

In other words, sum cells in A:A if the cell is less than 10. Or must
that
be written SUMIFS(A:A,A:A,"<10")?


2. Is SUMIFS(A:A,">0","<10") permissible?

In other words, sum cells A:A if the cell is greater than 0 and less than
10. Or must that be written SUMIFS(A:A,A:A,">0",A:A,"<10")?

Joe,

I have not tested it, but Excel 2007 has a specification of 255 "arguments
in a
function". Up to 127 range/criteria pairs are allowed.

Here is part of the description from HELP:

========================
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,
criteria2], .)

The SUMIFS function syntax has the following arguments (argument: A value
that
provides information to an action, an event, a method, a property, a
function,
or a procedure.):

sum_range Required. One or more cells to sum, including numbers or names,
ranges, or cell references (cell reference: The set of coordinates that a
cell
occupies on a worksheet. For example, the reference of the cell that
appears at
the intersection of column B and row 3 is B3.) that contain numbers. Blank
and
text values are ignored.

criteria_range1 Required. The first range in which to evaluate the
associated
criteria.

criteria1 Required. The criteria in the form of a number, expression,
cell
reference, or text that define which cells in the criteria_range1 argument
will
be added. For example, criteria can be expressed as 32, ">32", B4,
"apples", or
"32."

criteria_range2, criteria2, . Optional. Additional ranges and their
associated
criteria. Up to 127 range/criteria pairs are allowed.
======================================
--ron

 

[Ron Rosenfeld]

Excellent! This is much clearer then the WorksheetFunction.SUMIFS method
description that I stumbled across online at
http://msdn.microsoft.com/en-us/library/bb239775.aspx.

Thanks.

Based on that, I believe the answers to my questions are: we must do
SUMIFS(A:A,A:A,"<10") and SUMIFS(A:A,A:A,">0",A:A,"<10"). Please correct me
if I'm wrong.

I'm pretty sure that's correct.

Interesting. Does that mean the WorksheetFunction.SUMIFS method description
is incorrect? Or does that mean that WorksheetFunction.SUMIFS method is
more limited than the Excel function?

Say what?! I always thought that the WorksheetFunction methods are just a
way to access Excel functions. I thought the syntax and behavior are
identical because, in fact, we are executing the same function that Excel
uses, not look-alike work-alike code ("separate but equal" ;-).

Please tell me that assumption is correct. If it is not -- if the
WorksheetFunction method implementation is physically distinct from the
Excel implementation -- that opens a Pandora's box of potential trouble, no
matter what good might exist to keep the two implementations in sync when
changes are made.

I don't have time to check it out. On the face of it, it sure seems different.
I wonder if the number of arguments to a function did not get updated with VBA.
But the implementation also seems different.
--ron