Results 1 to 28 of 28

COUNTIF/SUMPRODUCT(?) to create a matrix from data

Threaded View

  1. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: COUNTIF/SUMPRODUCT(?) to create a matrix from data

    OK. I set up 6 Named ranges to make the formula look simpler. CTRL-F3 to view edit. They select the range of the table for each action needed in SUMPRODUCT.

    They are all based on the use of INDEX;MATCH to select the column of the arrival time the column to the left of it and the column to the right of it (A_Rng0, A_RngMinus and A_RngPlus) and similarly for the departure time. The SUMPRODUCT then looks like this:

    =IFERROR(IF(E$5<$D6,"",SUMPRODUCT((A_Rng0<>"")*(A_Rng0<>A_RngMinus)*(D_Rng0<>"")*(D_Rng0<>D_RngPlus))),"")

    REMEMBER the Named Ranges MUST extend ONE column to the RIGHT of the last column with data.

    N.B. You said: "the matrix shows four vehicles arriving at 0830 and leaving at 0915, whereas only three do (SD19, J333 and SL61)". In fact there are four. You forgot to count SH66.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sumproduct multiple criteria query
    By Millixcel in forum Excel General
    Replies: 3
    Last Post: 05-03-2018, 07:24 AM
  2. Create a new table (or matrix) from an exported set of data from a website.
    By dherrero in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-19-2014, 11:19 AM
  3. Replies: 6
    Last Post: 11-18-2013, 11:28 PM
  4. Trying to create an output grid/matrix with custom data.
    By JoeTheBro in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-28-2013, 10:47 AM
  5. Replies: 3
    Last Post: 10-18-2010, 11:09 AM
  6. Create a matrix from data in three column
    By sa02000 in forum Excel General
    Replies: 3
    Last Post: 06-27-2006, 09:30 PM

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