## Monday, August 20, 2012

### yogi_Perform Multi_Conditional Count For A Table Of Values

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #705   Aug 20, 2012     www.energyefficientbuild.com.

SUMPRODUCT resulting in zeros.. ( Count IF w/ Multiple Criteria)

I am attempting to use the SUMPRODUCT function on Google's Spreadsheet, but continue to get a zero as my end result. I can get my formula to work in Microsoft Excel, but not in Google Docs.   Can someone please help?
The Objective:

Record the amount of people who are enrolled/wait-listed and attended/cancelled in a specific course within a month.

Example:
How many people in Sales have enrolled and attended training in March 2012?

A)Team      B) Status   C) Course Date   D) Attendance
Sales       enrolled    3/3/2012         attended
Engineering enrolled    5/2/2012         cancelled
Sales       enrolled    4/5/2012         attended
Engineering wait-listed 3/28/2012        unknown

My Codes:
=SUMPRODUCT((C:C>=DATE(2012,3,1))*(C:C<=DATE(2012,3,31))*(D:D="Attended")*(A:A="Sales")*(B:B="Enrolled"))
Excel Format:
=COUNTIFS(A:A,"Sales",D:D,"attended",B:B,"enrolled",C:C,">="&DATEVALUE("03/1/2012"),C:C,"<="&DATEVALUE(("3/31/2012")))
A pivot table can solve this issue but my clients prefer using the methods above.
----------------------------------------------------------------------------------
following is a solution to the problem