# Excel For Underdogs

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.

### 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.

____________

### 4 Responses

1. Michael Pierce says:

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)

2. sam says:

For 2007 use CountIfs its much much faster but will work for AND criteria only

3. Andrew Engwirda says:

Hi Debra, that video is cool!

4. Tina says:

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.