Have you ever had trouble trying to count items based on two criteria? Don't worry – it's Underdog to the rescue!
Note: There's a short sound clip in the video, so turn down your speakers if your co-workers are napping.
Underdog Comes to the Rescue in Excel
Use SUMPRODUCT to Count
As Underdog mentioned, you can use the SUMPRODUCT function to count items using multiple criteria. In the video, the formula was:
=SUMPRODUCT(–(B2:B8="Sun"),–(C2:C8="Yes"))
There are other tips for counting in Excel on my Contextures website.
Also, Bob Phillips has extensive information about SUMPRODUCT on his xlDynamic.com website.
____________
Nice animation...I never knew Underdog was an Excel guy...
You can use SUMPRODUCT() to generate SUMIF() results with multiple criteria well. For example, if column D held the actual tip amounts, you could sum up the total tips from Sunday customers with the following formula:
=SUMPRODUCT(–(B2:B8="Sun"), –(C2:C8="Yes"), D2:D8)
For 2007 use CountIfs its much much faster but will work for AND criteria only
Hi Debra, that video is cool!
A B C
NICE 7/17/2009 F
NICE 7/17/2009 A
NICE ONE A (*)
NICE 6/12/2008 A (*)
NICE 2/24/2009 A (*)
NICE TWO A (*)
How to build a sumproduct formula to count date and text (*), when Col A=NICE and C=A and less than 7/17/2009, the answer should be 4.