Google Spreadsheet: Count values from last 30 days

Question

Here is a sample spreadsheet you can view and edit: https://docs.google.com/spreadsheet/ccc?key=0AnWwBCPp8kISdGc4bExyYV8xOTVRbnRkaXNQSzBWalE&usp=sharing.

How can I count the number of successes that have occurred in the past 7 or 30 days?

Answer 1

This is how I would do that.

Formula 1

1. COUNT(value_1)
2. FILTER(sourceArray, arrayCondition_1, arrayCondition_2, arrayCondition_3)
3. DATEVALUE(text)
4. TEXT(number, format)    

=COUNT(   
   FILTER(                                               == value_1
     A2:A14,                                             == sourceArray
     A2:A14 <> "",                                       == arrayCondition_1
     A2:A14 >=                                           == arrayCondition_2
       DATEVALUE(                                        == text
         TEXT(
           NOW()-30,                                     == number
           "MM/dd/yyyy"                                  == format
         )
       )
     B2:B14 = "Success"                                  == arrayCondition_3
   )
 )

Explained

The sourceArray is filtered by the first arrayCondition_1 by means of having an entry ("<>" = not equal and "" = an empty string). The second arrayCondition_2 will subtract, from the current date (without time), 30 days. This is compared to the sourceArray. The third arrayCondition_3 will filter column B for instances where it matches the word "Success". The COUNT function uses value_1 to count the instances and it will ignore #N/A (COUNTA doesn !!).

Formula 2

1. IFERROR(test, value)
2. QUERY(data, query, headers) 
3. TEXT(number, format)   

=IFERROR(
   QUERY(                                                == test
     A2:B14,                                             == data
     "SELECT COUNT(A)                                    == begin of query
      WHERE 
        A IS NOT NULL AND 
        B = 'Success' AND 
        A >= date '" & 
          TEXT(
            NOW()-30,                                    == number
            "yyyy-MM-dd"                                 == format
          ) & "'                                         == end of query
      LABEL COUNT(A) '' "                                == headers
   ), 
 "No successes"                                          == value
 )

Explained

The QUERY function is build, almost the same as the FILTER function in formula 1. The data is the same as the arraySource. The beginning of the query starts with a SELECT statement, which is related to SQL, followed by COUNT(A). The count is performed on column A, when column A contains something (IS NOT NULL) AND column B must match the string Success AND the dates from column A are greater equal (>=) to the current date minus 30 days:

---

Pay close attention to how the date is being prepared: Language Elements, Literals

Use the keyword date followed by a string literal in the format yyyy-MM-dd. Example: date "2008-03-18".

The TEXT function offers the possibility to format the date, as described above.

---

The header part starts with LABEL and calls for COUNT(A), which is column A. The replacement text of the header must be an empty string (''). You need to do that, because otherwise the header is included (as count). If the test of the IFERROR function yields an error (#N/A), then it must display the value No successes.

Remark

The first formula fooled me the first time, when I used the COUNTA function. This gives a count of 1 if there are no matches (@Nick5a1: thanks !!). Therefore I created the second formula, that ultimately lead to a revised first formula !!

Both formulae are quite logical to build. The catchy part for the first is to get rid of the time notation when you use NOW() and (very important) to use the COUNT function. For the second formula you need to really understand how the QUERY function works, but allows for a better error handling.

Ultimately I would chose for the second formula.

Example

See example file you created yourself.