Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I am working in Google Spreadsheets and 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  

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.

I am looking to find a formula that

  • Counts the number of unique articles that have been submitted (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)

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

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

share|improve this question

migrated from superuser.com Oct 22 '13 at 16:10

This question came from our site for computer enthusiasts and power users.

2 Answers 2

I don't think you can do it without an extra column. Here's my method.

In cell E2, enter

=COUNTIFS($A$1:A2,A2,$B$1:B2,B2)=1

and fill down the column. This counts the number of articles so far (i.e. it only looks above the current row) that match the one in that row, and compares the result to 1. So you can call that column 'is_unique' and count the number of TRUEs in that column.

=COUNTIF(E:E,TRUE)

Of course, for your other criteria, you should just add more arguments to the COUNTIFS formula above.

share|improve this answer
    
COUNIFS is Excel exclusive !! The OP is working on a Google Spreadsheet. –  Jacob Jan Tuinstra Oct 22 '13 at 15:08
    
COUNTIFS is available in new Google Sheets and returns 4 for the example data (as does countif(C:C,1)). –  pnuts Jun 18 at 19:33

Build a pivot table. No extra columns needed. You can then manipulate the pivot table's filters to get you all the things you are looking for. Like this:

enter image description here

share|improve this answer
    
The OP is working in a Google Spreadsheet? This looks like Excel? –  Jacob Jan Tuinstra Oct 22 '13 at 15:10
    
New Google Sheets can create a table very similar to the one shown above. –  pnuts Jun 18 at 19:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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