Sumif / Countif

  • Thread starter Thread starter Blue
  • Start date Start date
B

Blue

Hi
is there any way that i can use 2 arguments for sumif & countif functions
instead of just 1?
 
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.
 
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.
 
Yes, SUMPRODUCT. Countless examples can be seen in the archives of this
newsgroup.
 
Yes, SUMPRODUCT. Countless examples can be seen in the archives of this
newsgroup.
 
Back
Top