will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
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"))
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.
____________
June 17th, 2009 at 12:23 pm
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)
June 18th, 2009 at 4:40 am
For 2007 use CountIfs its much much faster but will work for AND criteria only
July 1st, 2009 at 6:57 pm
Hi Debra, that video is cool!
July 18th, 2009 at 2:42 am
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.