+ Reply to Thread
Results 1 to 2 of 2

IF or CountIf Functions across large Data Fields

  1. #1
    Registered User
    Join Date
    04-09-2021
    Location
    New York
    MS-Off Ver
    2013
    Posts
    1

    IF or CountIf Functions across large Data Fields

    Hello,

    I am working with a dataset that has about 400,000 rows of data. In that data there are columns that tell me in which city a transaction occurred, and how many transactions occurred on a particular day.

    What I want to do is add a variable column that has an IF function (or maybe Countif) so I can filter the entire data set (in my pivot table) by a certain set of cities. The issue is that there about 200 cities that I want to filter on, and when I try to fun the countif functions, my excel crashes.

    Any easy way to filter my dataset to only include the 200 or so cities that I'm interested in?

    Thanks

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: IF or CountIf Functions across large Data Fields

    If you had your 200 cities listed in a column somewhere (suppose it is column X), then you could try this in your helper column:

    =IF(COUNTIF(X$1:X$200,A2),"Yes","No")

    or this one:

    =IF(ISNUMBER(MATCH(A2,X$1:X$200,0)),"Yes","No")

    assuming the cities in your data are in column A starting in A2. Copy this down as required, then you can filter the helper column on "yes".

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Transpose large amounts of data with multiple fields
    By Pierceber in forum Excel General
    Replies: 8
    Last Post: 03-23-2016, 03:24 PM
  2. IF Functions with Small and Large Functions? Not Sure
    By HDBrewer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-11-2016, 03:56 AM
  3. EXCEL Functions Does not Work for Large Data
    By nosense in forum Excel General
    Replies: 25
    Last Post: 02-23-2015, 08:14 PM
  4. Replies: 0
    Last Post: 11-21-2014, 12:34 PM
  5. Replies: 1
    Last Post: 08-13-2014, 08:56 AM
  6. Replies: 2
    Last Post: 03-18-2006, 09:10 PM
  7. Replies: 12
    Last Post: 03-17-2006, 01:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1