Conditional SUM Formulas

=SUMIFS(Amount, DateDue,">="&DATE(2007,5,1)) works OK

SUMIFS syntax is SUM Range, Criteria range1, Criteria1,Criteria range2, Criteria2,

"DueDate" is Criteria1 Range

">="&DATE(2007,5,1) is criteria1

Why isn't it written like this?


1) ">=&DATE(2007,5,1)"

or

2) ">=&"DATE(2007,5,1)

3) Can we write the formula on the cell A32 with SUMIFS and SUMIF?

In the first case that would take the literal string &DATE(2007,5,1) as the value to be compared with. The second case is syntactically incorrect as you have the string ">=&" followed by the DATE function (which returns a number), and nothing to "join" them together.

Also, see your other thread on this topic.

Hope this helps.

Pete

Re question #3

=SUM(SUMIFS(Amount,Difference,"<0",Office,{"Oregon","California"}))

SUMIFS will create a result for each criterion i.e, Oregon then California and then we SUM the results.

Who does this formula won't work for A32?

Formula:

Please Login or Register  to view this content.

However the result is different with that formula. (16722.45) Why?

Formula:

Please Login or Register  to view this content.

That formula won't work. The result is different than the original formula.

Depends what you expect the formula to do: reading you comments in your workbook it is hard to determine whether you mean logical "OR" or logical "AND"

If you want the Differences for Oregon then the above will not work: what is required is an AND condition using SUMIFS as there are 2 criteria.

The formula on the cell A33 looks like faulty to me.

1)"Amount" only multiplied with the cells of which Office Column is Oregon. The cells of which Difference Column <0 are skipped. Isn't it faulty?

2)Isn't there a paranthesis next to "SUM" missing? Because after "+" there is not another "SUM".

I think it should have been like this;

Formula:

Please Login or Register  to view this content.