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.

user Googleonline said: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/LEAKlEFvGvg)
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

No comments:

Post a Comment