Sumif / Countif

[Blue]

Hi
is there any way that i can use 2 arguments for sumif & countif functions
instead of just 1?

 

[Luke M]

Sorta. You need to use the SUMPRODUCT formula
Example of summing everything that corresponds to "tom" or "bob"
=SUMPRODUCT(((A2:A10="Tom")+(A2:10="Bob"))*(B2:B10)
Conditions in multiple columns (Bob in column A, Car in column C
=SUMPRODUCT((A2:A10="Bob")*(C2:C10="Car")*(B2:B10))

Similarly, a count of instances of Bob in column A, Car in column C
=SUMPRODUCT((A2:A10="Bob")*(C2:C10="Car"))
Or count in same column
=SUMPRODUCT(((A2:A10="Tom")+(A2:10="Bob"))

Note that you can't callout entire columns (A:A), and ranges must be the
same size.
Also, XL 2007 introduced SUMIFS and COUNTIFS, which allows multiple criteria.

 

[Luke M]

Sorta. You need to use the SUMPRODUCT formula
Example of summing everything that corresponds to "tom" or "bob"
=SUMPRODUCT(((A2:A10="Tom")+(A2:10="Bob"))*(B2:B10)
Conditions in multiple columns (Bob in column A, Car in column C
=SUMPRODUCT((A2:A10="Bob")*(C2:C10="Car")*(B2:B10))

Similarly, a count of instances of Bob in column A, Car in column C
=SUMPRODUCT((A2:A10="Bob")*(C2:C10="Car"))
Or count in same column
=SUMPRODUCT(((A2:A10="Tom")+(A2:10="Bob"))

Note that you can't callout entire columns (A:A), and ranges must be the
same size.
Also, XL 2007 introduced SUMIFS and COUNTIFS, which allows multiple criteria.

 

[David Biddulph]

Yes, SUMPRODUCT. Countless examples can be seen in the archives of this
newsgroup.

 

[David Biddulph]

Yes, SUMPRODUCT. Countless examples can be seen in the archives of this
newsgroup.

 

[Teethless mama]

If you are using xl2007, you can use SUMIFS, and COUNTIFS functions

 

[Teethless mama]

If you are using xl2007, you can use SUMIFS, and COUNTIFS functions