Results 1 to 28 of 28

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

Threaded View

  1. #5
    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

    This looks horrible. It is horrible. But... it does seem to work... I couldn't think of a prettier way to do it!!

    Someone will do it with a punchy one-line formula, and put me to shame!!!

    Formula: copy to clipboard
    =IFERROR(SUMPRODUCT(
    (INDEX(data!$F$6:$P$6,,MATCH(output!$D6,data!$F$5:$P$5,0)):INDEX(data!$F$23:$P$23,,MATCH(output!$D6,data!$F$5:$P$5,0))<>"")*
    (INDEX(data!$F$6:$P$6,,MATCH(output!$D6,data!$F$5:$P$5,0)-1):INDEX(data!$F$23:$P$23,,MATCH(output!$D6,data!$F$5:$P$5,0)-1)<>
    INDEX(data!$F$6:$P$6,,MATCH(output!$D6,data!$F$5:$P$5,0)):INDEX(data!$F$23:$P$23,,MATCH(output!$D6,data!$F$5:$P$5,0)))*
    (INDEX(data!$F$6:$P$6,,MATCH(output!E$5,data!$F$5:$P$5,0)):INDEX(data!$F$23:$P$23,,MATCH(output!E$5,data!$F$5:$P$5,0))<>"")*
    (INDEX(data!$F$6:$P$6,,MATCH(output!E$5,data!$F$5:$P$5,0)+1):INDEX(data!$F$23:$P$23,,MATCH(output!E$5,data!$F$5:$P$5,0)+1)<>
    INDEX(data!$F$6:$P$6,,MATCH(output!E$5,data!$F$5:$P$5,0)):INDEX(data!$F$23:$P$23,,MATCH(output!E$5,data!$F$5:$P$5,0)))),"")
    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