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

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)
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