I'm having trouble with COUNTIFS on Google Sheets. I've been able to use SUMIFS and now I need to use COUNTIFS.

I tried to do a Find and Replace, changing SUMIFS to COUNTIFS, on what I thought were similar criteria.

What are the differences between parameters on SUMIFS and COUNTIFS? More importantly, how do I make the formula work?

Some context:

I'm working on a dashboard that consolidates website and social media analytics data from several offices around the world.

We are using Google Sheets for easier collaboration among the communications managers at each office.

The dashboard needs to present a table of a number of metrics on a monthly basis. We archive the dashboard at the end of each year, then present to senior management. The data is used to inform global communications strategy.

Each office maintains its own website and social media accounts. When the dashboard was initially set up, data was manually entered for all fields.

Then I installed the Google Analytics add-on and designed the following formula to populate the table:

In a separate sheet we run the raw data from the Google Analytics add-on (called 'Canada Raw Data'). The dates run from A15 onward. In the January column in the tab in the screenshot, it basically says, "if the date is after Jan 1 and before Jan 31, include the value in the sum." To keep the table tidy, I've wrapped it in ISERROR.

Rinse and repeat for Feb-Dec.

Where I get stuck is in Twitter. I've downloaded a CSV from Twitter Analytics for all tweets so far in January and copied these values to a sheet called 'Canada Twitter Raw Data'.

Then I added a column "date" to pull the left-most ten characters from Twitter's "time" and tell Sheets to understand that it's a date:

=DATEVALUE(LEFT(E2,10))

After a few Find and Replaces, I was able to replicate the formula for the new ranges with relative simplicity.

It worked when I was still using SUMIFS. See it working in the Retweets cell C23:

There are two countifs commands there. Try to isolate the issue to one.
– user79865Jan 27 '16 at 23:36

2

It might also help to share a spreadsheet where the problem occurs (perhaps not the original one, but a copy of the relevant part.)
– user79865Jan 27 '16 at 23:53

@Minestrone thanks for helping. Take a look at a copy of the sheet here: docs.google.com/a/amrefcanada.org/spreadsheets/d/… Isolating the countifs helped me re-think the logic for the count formula. I've switched it to this: =COUNTA('Canada Twitter Raw Data'!C2:C2840,'Canada Twitter Raw Data'!A2:A2840=1/1/2016<>1/31/2016) Which almost works (it counts 36 tweets, instead of what I think should be 35).
– Sean PowerJan 28 '16 at 12:08

We can't access that spreadsheet. Can you fix permissions ?
– JPVJan 28 '16 at 14:58