+ Reply to Thread
Results 1 to 5 of 5

VLOOKUP - Counting Instances

  1. #1
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    VLOOKUP - Counting Instances

    See the attached spreadsheet:

    Please provide guidance in how I would do the following, and thank you for your time and efforts. It is greatly appreciated

    Sheet1 should be a VLOOKUP to COUNT the # of Incidents (Column F of the Log tab) for each Customer (A, B, and C of the Log spreadsheet) and display the value in Column B of Sheet1 (expected value highlighted in blue). An Incident is when Column F (of the Log tab) = "Incident"

    Sheet1 should be a VLOOKUP to COUNT the # of Incidents in 3 months (Column F of the Log tab) for each Customer (A, B, and C of the Log spreadsheet) and display the value in Column G of Sheet1. The only difference here is that it should count only those incidents in the last 3 months (expected value highlighted in yellow). An Incident is when Column F (of the Log tab) = "Incident" and the Incident Date (of the Log tab) is Column D

    Sheet1 should be a VLOOKUP to COUNT the # of Late Deliveries (Column C of the Log tab) for each Customer (A, B, and C of the Log tab) and display the value in Column C of Sheet1. A late shipment is when Column C = "Late Shipment" or "Delayed Shipment". The expected value is highlighted in orange.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    Re: VLOOKUP - Counting Instances

    Just wanted to check with the day crowd on these questions above.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: VLOOKUP - Counting Instances

    No of incidents:

    =COUNTIFS(Log!$A:$A,Sheet1!$A6,Log!$F:$F,"Incident")

    Late deliveries

    =SUM(COUNTIFS(Log!A:A,Sheet1!$A6,Log!C:C,{"Late Shipment","Delayed Shipment"}))

    Incidents in last 3 months

    =COUNTIFS(Log!$A:$A,Sheet1!$A6,Log!$F:$F,"Incident",Log!D:D,">="&TODAY()-90)

    The latter is the same as "No. of incidents" since ALL occurred within 90 days of TODAY() unless you exclude current month (?)

  4. #4
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    Re: VLOOKUP - Counting Instances

    The latter is the same as "No. of incidents" since ALL occurred within 90 days of TODAY() unless you exclude current month (?)
    I would include the current month in this 'within 90 days (3 months) of today so you're correct, Customer A should have '2' incidents within 90 days.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: VLOOKUP - Counting Instances

    Has your query been answered?

+ 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] counting instances
    By bzl in forum Excel General
    Replies: 8
    Last Post: 08-02-2014, 11:14 AM
  2. [SOLVED] Counting multiple instances.
    By rabbina in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-04-2014, 10:30 AM
  3. [SOLVED] Complex VLOOKUP, multiple instances vertical, return subsequent instances horizontally
    By Miles_2804 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-12-2013, 11:54 AM
  4. Counting instances of same record
    By g_mateus in forum Excel General
    Replies: 2
    Last Post: 06-04-2009, 02:51 AM
  5. Counting multiple instances - same value
    By polishmf in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 12-15-2006, 11:08 AM
  6. Counting instances of an offset value???
    By Simon Lloyd in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-12-2005, 04:37 AM
  7. counting instances of decimals
    By Eden in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-21-2005, 11:23 AM

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