Wednesday, February 20, 2013

yogi_Perform Multi-Conditional Count In Another Sheet



                                          Google Spreadsheet   Post  #1039
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 20, 2013
user Monica Rampani said:(http://productforums.google.com/forum/?zx=icnslbsw6sms#!category-topic/docs/spreadsheets/KTDN9AnBfKo)
Array Formula based on Date Range and Multiple Criteria
Any help would be much appreciated.  I have the following spreadsheet:

A                              B              C              D             E
Timestamp         Username FA Code  Date  Installed
1/30/2013 18:55:33 james 10131150 1/30/2013 Yes
1/31/2013 8:49:15 james 10005063 1/31/2013 No
1/31/2013 10:09:06 derek 10131135 1/31/2013 Yes
1/31/2013 10:13:04 james 10150588 1/13/2013 No
1/31/2013 10:57:13 james 10149630 1/31/2013 No

This information is on the first sheet called "Job Log Submissions".  On another tab I have a sheet called "Stats".  What I want to do is have a formula that will tell me how many entries each Username Installed within a date range from column A. 

For example, I want to know how many sites did James install (a "yes" answer) between 1/30/2013 and 2/2/2013 based on column A (not column D.)  The answer with the above example should be 1.  

I have been playing around with Array Formulas but can't seem to get it right.  This is what I tried:

=ArrayFormula(SUM((‘Job Log Submissions'!A:A>=date(2013;1;30))*(‘Job Log Submissions'!A:A<=date(2013;2;2))*(‘Job Log Submissions'!B:B="james")*(‘Job Log Submissions'!E:E="Yes")))

But it is giving me an Error, maybe because I need to incorporate the time as well? Would anyone know how to achieve this? Thanks in advance
----------------------------------------------------------------------------------------------------------------
following is a solution to the problem