SunIf

[RickK]

Can a sumif (or countif) function be based upon 2 criteria?
For example if A1:A50 might contain the manufacturing
month (from a validation list) and B1:B50 contained the
item type "widget" or "cog" (also from a validation list),
I'd like to return the total number of widgets
manufactured in June, throughtout this entire range.

 

[Felipe]

Rick,

For Countif:
=SUMPRODUCT((A1:A50=month)*(B1:B50)=item_type))
For SUMIF (If the numer of items is in C1:C50:
=SUMPRODUCT((A1:A50=month)*(B1:B50)=item_type)*C1:C50)

Regards,
Felipe

 

[Jason Morin]

For multiple criteria, you need to construct an array
formula or use SUMPRODUCT. To count the no. of widgets
made in June:

=SUMPRODUCT((A1:A50="June")*(B1:B50="widget"))

=SUM((A1:A50="June")*(B1:B50="widget"))

The second formula is an array formula which requires that
you press ctrl/shift/enter for it to work. Excel will
place {} around the formula.

To sum the number of widgets made in June (assuming qty in
C1:C50):

=SUMPRODUCT((A1:A50="June")*(B1:B50="widget")*C1:C50)

=SUM((A1:A50="June")*(B1:B50="widget")*C1:C50)

HTH
Jason
Atlanta, GA