2

Here is a link to a sample spreadsheet:

https://docs.google.com/spreadsheets/d/1LcfqPxsnQGEVZKd2imM1WPYRvgbZBiEQ18mHVd3znjE/edit?usp=sharing

What I want to do is count all unique values in column C (ID) and all values in column A (initial email) in the month of November.

This is the best I could come up with:

=COUNTUNIQUE(FILTER(Sample!C2:C,Sample!A2:A="Mon, Nov. 23, 2015 2PM"))

But it would only count one specific date in November so I would have to also count all the other dates and then total them. Is there a way for the formula to count all unique values in column C and count all values in Column A containing "Nov"? To add to that, I also need to count all values in Column F (Response category), containing unique ID for the month of November.

  • I can't see your spreadsheet because you didn't allow others to view it. Click "Share" in upper right. – user79865 Dec 3 '15 at 17:04
  • Also, the last sentence, with "containing unique ID for the month of November" is unclear. You may want to explain what exactly those values should contain. – user79865 Dec 3 '15 at 17:31
  • I just shared the link and I'm trying out the formula right now. – Vee Dec 4 '15 at 3:37
  • No, I still can't see it. Try opening the link in a private browser window, to see how it appears to me. – user79865 Dec 4 '15 at 5:12
  • The link should finally be shared. I used the regexmatch but couldn't seem to get the right number. Please have a look at the 'statistics' tab. – Vee Dec 4 '15 at 14:58
0

You can filted by regexmatch instead of equality.

=countunique(filter(Sample!C2:C, regexmatch(Sample!A2:A, "Nov")))

Matching the regular expression "Nov" simply means containing "Nov" somewhere in the string.

  • I tried the "regexmatch" formula (please see "Statistics" tab) but couldn't get the right number. It should have been 12 but the formula counted only 9. – Vee Dec 4 '15 at 5:02
  • If I just do a pure 'countunique' on the Nov data, I get 9. It seems right to me. Example here - listing unique and counting for just Nov – Tom Woodward Dec 4 '15 at 16:03
  • @Vee 9 is the correct number of unique values for all November dates. If you count unique values for each particular date separately, and then add, that's a different count. E.g., you can have 550 on Nov 3 and 550 on Nov 9. In a count for all of November, it's 1. Counting separately by dates and adding, it's 1+1 = 2. – user79865 Dec 4 '15 at 16:34
  • @NormalHuman That makes sense to me now. Thank you for the explanation. – Vee Dec 8 '15 at 2:46
  • @TomWoodward Thanks for the response. You (and NormalHuman) both helped me. – Vee Dec 8 '15 at 2:47

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.