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.

Improve this question
7
  • I can't see your spreadsheet because you didn't allow others to view it. Click "Share" in upper right.
    – user79865
    Dec 3, 2015 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, 2015 at 17:31
  • I just shared the link and I'm trying out the formula right now.
    – Vee
    Dec 4, 2015 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, 2015 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, 2015 at 14:58

1 Answer 1

Reset to default
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.

Improve this answer
5
  • 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, 2015 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, 2015 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, 2015 at 16:34
  • @NormalHuman That makes sense to me now. Thank you for the explanation.
    – Vee
    Dec 8, 2015 at 2:46
  • @TomWoodward Thanks for the response. You (and NormalHuman) both helped me.
    – Vee
    Dec 8, 2015 at 2:47

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.