How To Count Duplicates in Google Spreadsheets (COUNTIF)

Updated: 23 December, 2019 seen 10,469

I've been using Google Spreadsheets literally for everything that can be counted or measured (dividend income, stock trades, body weight and so on). 

And so did when I decided to build an option trading journal I built it using Google Spreadsheets.

Everything good here, except I decided to count how many trades there have been, how many are open, how many are closed, how many are winning, how many are losing and based on that make a simple calculation to figure out what is my winning rate

Here is the final result:

Win ratio calculation using Google Spreadsheets

Win ratio calculation using Google Spreadsheets

COUNT DUPLICATES WITH COUNTIF

COUNTIF is a relatively basic Google Sheets function that counts cells that include numbers or text based on a specified condition. The syntax is simple; you only need to provide a cell range and criterion for which cells to count. You can enter the COUNTIF function in the fx bar with the syntax: `=COUNTIF(range, criterion)`. Source: How To Count Duplicates in Google Sheets

Spreadsheet setup

  • Column L - Trade status (open/closed)
  • Column M - Winner / Loser

Now to make count for open trade status:

=COUNTIF(L:L, "open")

closed:

=COUNTIF(L:L, "closed")

winning trades:

=COUNTIF(M:M, "winner")

losing trades:

=COUNTIF(M:M, "loser")

Works like a charm. 

about

Hi! My name is Reinis Fischer (38). Dad, husband, options traderdividend income investorcrypto hedge fund manager, drone enthusiast, world traveler, photographer, and passionate lover of Georgian cuisine (vegetarian). 

If you're looking to diversify your investment portfolio and explore the exciting world of cryptocurrency, be sure to check out my crypto hedge fund for opportunities that align with your financial goals