Google Spreadsheet Formula Question

in #forumla7 years ago (edited)

Lets say I have the following in a spreadsheet in Columns A, B & C:

HEADER ROW:
SEARCH TERM | IP ADDRESS | SEARCHED ON

EXAMPLE DATA:
iPhone10 | 51.255.65.20 | 2018-02-08 1:38:56
Shoes | 216.344.46.55 | 2018-02-20 0:14:03
Satellites | 105.216.96.122 | 2017-09-19 0:53:07
Satellites | 100.43.85.11 | 2017-06-20 21:10:07
Video songs | 108.140.38.3 | 2017-07-26 5:52:54

In column D, I need the formula that will count the number of times the 'SEARCH TERM' appears, so the example data above should show like this after applying the formula:

iPhone10 | 51.255.65.20 | 2018-02-08 1:38:56 | 1
Shoes | 216.344.46.55 | 2018-02-20 0:14:03 | 1
Satellites | 105.216.96.122 | 2017-09-19 0:53:07 | 2
Satellites | 100.43.85.11 | 2017-06-20 21:10:07 | 2
Video songs | 108.140.38.3 | 2017-07-26 5:52:54 | 1

Thanks

Sort:  
Loading...

One of my friends answered this which really helped me out:

=======================
=COUNTIF(A:A,Sheet1!A2)
=======================

(sheet 1 is the google drive sheet)

Let's say you have the values spread across columns A,B,C.

A1 has the value "iPhone10", A2 "Shoes" and so, on.

If you enter the below formula in column D, in cell C1, you will get the results.

=COUNTIF(A:A,Sheet1!A1)

Sheet1 is actually the name of the google drive sheet (displayed at the bottom).

Now if you drag the formula in C1 down to other cells, you'll get the desired results!

Congratulations @bollywood! You received a personal award!

Happy Birthday! - You are on the Steem blockchain for 2 years!

You can view your badges on your Steem Board and compare to others on the Steem Ranking

Do not miss the last post from @steemitboard:

New japanese speaking community Steem Meetup badge
Vote for @Steemitboard as a witness to get one more award and increased upvotes!