Hi

The SUMIF function allows you to sum a range using one criteria.

=SUMIF(a1:a11,"New York",b1:b11)

In plain English sum range B1 to B11 if in the corresponding row of A1
to A11 the value is "New York". You can use only ONE criteria

With SUMPRODUCT the same formula would look like this

=SUMPRODUCT((a1:a11="New York")*(B1:B11))

But you can also have many criterias like in:

=SUMPRODUCT((a1:a11="January")*(B1:B11="Product1") *(C1:C11="New
York")*(D1:D11="Store1")*(E1:E11))

In plain English sum range E1 to E11 if in the corresponding row of A1
to A11 the value is "January" and if in the corresponding row of B1
to B11 the value is "Product1" and if in the corresponding row of C1
to C11 the value is "New York" and if in the corresponding row of D1
to D11 the value is "Store1" and if in the corresponding row of A1 to
A11 the value is "a"

When you discover the SUMPRODUCT formula, you can forget about COUNTIF
and SUMIF.

Also when you discover INDEX/MATCH you can replace VLOOKUP, HLOOKUP
and LOOKUP.

See these amazing formulas at work at:

http://www.excel-vba.com/index-agent.htm


Pierre Leclerc
http://www.excel-vba.com