# Getting COUNTIFS to work in complicated Google Sheets formula

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:

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.

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

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.

• There are two countifs commands there. Try to isolate the issue to one.
– user79865
Jan 27, 2016 at 23:36
• 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). Jan 28, 2016 at 12:08
• We can't access that spreadsheet. Can you fix permissions ?
– JPV
Jan 28, 2016 at 14:58

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