counting unique values based on multiple columns

Posted by gooogalizer on Super User See other posts from Super User or by gooogalizer
Published on 2013-10-11T06:23:06Z Indexed on 2013/10/22 15:57 UTC
Read the original article Hit count: 266

I am working in google spreadsheets and I am trying to do some counting that takes into consideration cell values across multiple cells in each row.

Here's my table:

   |AUTHOR|    |ARTICLE|        |VERSION|   |PRE-SELECTED|   
    ANDREW      GOLF STREAM          1          X
    ANDREW      GOLF STREAM          2          X
    ANDREW      HURRICANES           1      
    JOHN        CAPE COD             1          X
    JOHN        GOLF STREAM          1  

(Google doc here)

Each person can submit multiple articles as well as multiple versions of the same article. Sometimes different people submit different articles that happen to be identically named (Andrew and John both submitted different articles called "Golf Stream").

Multiple versions written by the same person do not count as unique, but articles with the same title written by different people do count as unique.

So, I am looking to find a formula that

  • Counts the number of unique articles that have been submitted [4] (without having to manually create extra columns for doing CONCATS, if possible)

It would also be great to find formulas that:

  • Count the number of unique articles that have been pre-selected (marked "X" in "PRE-SELECTED" column) [2]

  • Count the number of unique articles that have only 1 version [4]

  • Count the number of unique articles that have more than 1 of their versions pre-selected 1

Thank you so much!

Nikita

© Super User or respective owner

Related posts about worksheet-function

Related posts about google-spreadsheets