2

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:

=IF(ISERROR(IF(SUMIFS('Canada Raw Data'!$D$15:$D$2840,'Canada Raw Data'!$A$15:$A$2840,">=1/1/2016",'Canada Raw Data'!$A$15:$A$2840,"<=01/31/2016")=0,"",SUMIFS('Canada Raw Data'!$D$15:$D$2840,'Canada Raw Data'!$A$15:$A$2840,">=1/1/2016",'Canada Raw Data'!$A$15:$A$2840,"<=01/31/2016"))),"",IF(SUMIFS('Canada Raw Data'!$D$15:$D$2840,'Canada Raw Data'!$A$15:$A$2840,">=1/1/2016",'Canada Raw Data'!$A$15:$A$2840,"<=01/31/2016")=0,"",SUMIFS('Canada Raw Data'!$D$15:$D$2840,'Canada Raw Data'!$A$15:$A$2840,">=1/1/2016",'Canada Raw Data'!$A$15:$A$2840,"<=01/31/2016")))

Here is a screenshot that might help:

A screenshot showing one country's dashboard. Metrics along the left, months along the top. Please don't worry about the fact that we're tracking vanity metrics at this point--yes, we know they are mostly meaningless.

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:

=IF(ISERROR(IF(SUMIFS('Canada Twitter Raw Data'!$I$2:$I$2840,'Canada Twitter Raw Data'!$A$2:$A$2840,">=1/1/2016",'Canada Twitter Raw Data'!$A$2:$A$2840,"<=01/31/2016")=0,"",SUMIFS('Canada Twitter Raw Data'!$I$2:$I$2840,'Canada Twitter Raw Data'!$A$2:$A$2840,">=1/1/2016",'Canada Twitter Raw Data'!$A$2:$A$2840,"<=01/31/2016"))),"",IF(SUMIFS('Canada Twitter Raw Data'!$I$2:$I$2840,'Canada Twitter Raw Data'!$A$2:$A$2840,">=1/1/2016",'Canada Twitter Raw Data'!$A$2:$A$2840,"<=01/31/2016")=0,"",SUMIFS('Canada Twitter Raw Data'!$I$2:$I$2840,'Canada Twitter Raw Data'!$A$2:$A$2840,">=1/1/2016",'Canada Twitter Raw Data'!$A$2:$A$2840,"<=01/31/2016")))

Twitter Find and Replaces still work with SUMIFS on Retweets.

What I need to know to populate C19 is how many tweets were sent in January. I wanted to count the Permalinks using the same approach as SUMIFS:

=IF(ISERROR(IF(countifs('Canada Twitter Raw Data'!$C$2:$C$2840,'Canada Twitter Raw Data'!$A$2:$A$2840,">=1/1/2016",'Canada Twitter Raw Data'!$A$2:$A$2840,"<=01/31/2016")=0,"",countifs('Canada Twitter Raw Data'!$C$2:$C$2840,'Canada Twitter Raw Data'!$A$2:$A$2840,">=1/1/2016",'Canada Twitter Raw Data'!$A$2:$A$2840,"<=01/31/2016"))),"",IF(countifs('Canada Twitter Raw Data'!$C$2:$C$2840,'Canada Twitter Raw Data'!$A$2:$A$2840,">=1/1/2016",'Canada Twitter Raw Data'!$A$2:$A$2840,"<=01/31/2016")=0,"",countifs('Canada Twitter Raw Data'!$C$2:$C$2840,'Canada Twitter Raw Data'!$A$2:$A$2840,">=1/1/2016",'Canada Twitter Raw Data'!$A$2:$A$2840,"<=01/31/2016")))

Yet, as you can see, the cell is still blank. Removing the ISERROR wrapping gets me the #N/A error you see in D19, which contains the following formula:

=(IF(countifs('Canada Twitter Raw Data'!$C$2:$C$2840,'Canada Twitter Raw Data'!$A$2:$A$2840,">=1/1/2016",'Canada Twitter Raw Data'!$A$2:$A$2840,"<=01/31/2016")=0,"",countifs('Canada Twitter Raw Data'!$C$2:$C$2840,'Canada Twitter Raw Data'!$A$2:$A$2840,">=1/1/2016",'Canada Twitter Raw Data'!$A$2:$A$2840,"<=01/31/2016")))

The error message in the #N/A:

Error COUNTIFS expects all arguments after position 2 to be in pairs.

What's the best way to populate that Tweets field for each month? Let me know if you would make any other changes to my formulas.

Improve this question
6
  • 1
    There are two countifs commands there. Try to isolate the issue to one.
    – user79865
    Jan 27, 2016 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.)
    – user79865
    Jan 27, 2016 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 Power
    Jan 28, 2016 at 12:08
  • We can't access that spreadsheet. Can you fix permissions ?
    – JPV
    Jan 28, 2016 at 14:58
  • Sorry about that. Can you please try this and let me know if it works? docs.google.com/spreadsheets/d/…
    – Sean Power
    Jan 28, 2016 at 18:05

1 Answer 1

Reset to default
2

You can do a count for each month by transforming an array formula to only count based on that month:

=COUNTIF(ARRAYFORMULA(TEXT('Canada Twitter Raw Data'!A2:A,"MM")),"01")

change the "01" for each month so february would look like this:

=COUNTIF(ARRAYFORMULA(TEXT('Canada Twitter Raw Data'!A2:A,"MM")),"02")
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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