1

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

Sample Data

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

3

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.

  • Hi again @jacobjantuinstra. That works perfectly for recording the number os successes in the previous calendar month. Unfortunately I need a formula that counts the number of successes in the past 30 days. I've updated the sample data to include some of those dates. For example, given today's date is the 4th of November, it should include successes from the 1st-4th of November, but not include successes from the 1st to 4th of October. How would I update the formula to do that? – Nick5a1 Nov 4 '13 at 20:52
  • Thanks for the help. The change you made seems to be counting the current month only, ie just the days in November that haven't happened yet. That's great but I also want to count the successes from the previous month from the current date onwards (current date minus 1 month). So as today is the 4th of November, it should also count successes from the 5th of October to the end of October, as well as the successes from the current month that it is already counting. – Nick5a1 Nov 4 '13 at 21:47
  • I just noticed something strange with this formula. If the answer is 1 or higher it displays the correct value, but if the answer is 0 it displays a value of 1. – Nick5a1 Nov 4 '13 at 22:38
  • also I'm curious, what does the <>"" mean in arrayCondition_1? – Nick5a1 Nov 5 '13 at 2:47
  • @Nick5a1 see revision I made. Good luck !! – Jacob Jan Tuinstra Nov 5 '13 at 7:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.