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.

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”))

Sumproduct

There are other tips for counting in Excel on my Contextures website.

____________

You may also like...

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.

Leave a Reply to Michael Pierce Cancel reply

Your email address will not be published. Required fields are marked *