+ Reply to Thread
Results 1 to 4 of 4

How to get the counts for different cities in a certain period.

  1. #1
    Registered User
    Join Date
    05-03-2017
    Location
    Rijen, Netherlands
    MS-Off Ver
    2011
    Posts
    11

    How to get the counts for different cities in a certain period.

    I want to determine how many letters were send per city each period of the year (kwartal, half year etc. )

    Sheet 1
    Column H contains different cities (about 10-12 differtent cities). In this column a certain city can appear more than once.
    Column Q contains different dates on which letters were send. The dates varie over the year.

    Sheet 2
    A1 contains the start date of the period.
    B1 contains the end date of the period

    In this sheet 2 I want to count the letters that were send to each city in each period of the year, depending on data and refering to that data in cells A1 and B1 so that I can report the amount of letters send. With the cells A1 and B1 I want the get the results I want.
    I tried to do this with the countif function and sumproduct though without succes.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to get the counts for different cities in a certain period.

    Almost certainly you could do this with a Pivot Table and avoid using functions altogether, although a COUNTIFS() function should work equally well albeit without the additional analysis flexibility a PT would bring.

    Upload the workbook if you want a more definitive answer.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    05-03-2017
    Location
    Rijen, Netherlands
    MS-Off Ver
    2011
    Posts
    11

    Re: How to get the counts for different cities in a certain period.

    The data is constantly changing and a pivot table doesn't update I've heard.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to get the counts for different cities in a certain period.

    That's not entirely correct.

    It's true that a PT won't update unless you choose to refresh it in some way.

    But you can have it react to an event like say a SheetChange event or a SheetActivate event such that it does update automatically. And if you use a dynamic range name that caters for constantly changing data then a simple one line macro is all you need.

+ 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. [SOLVED] Calculate the portion of a planned number during a period, based on another time period
    By BryGuy81 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2017, 01:29 PM
  2. counting cities
    By TheVolkinator in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-26-2014, 02:35 PM
  3. How to generate all possible combinations of nine cities
    By Nikolas1988 in forum Excel General
    Replies: 2
    Last Post: 06-09-2014, 09:21 AM
  4. Filterin of Cities in addresses
    By btiongsonjr in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-13-2013, 10:04 PM
  5. Macro to filter cities
    By rafuk73 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-31-2013, 09:56 AM
  6. [SOLVED] Complicated:Calculate time period based on no. of units sold + fluctuations within period
    By omaha.crab in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2012, 03:04 AM
  7. Many Cities into few Regions
    By Johnny Image in forum Excel General
    Replies: 2
    Last Post: 04-17-2012, 07:25 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