+ Reply to Thread
Results 1 to 4 of 4

Want to match data row on 2 cols of criteria, then display value in 3rd colmun as output

Hybrid View

  1. #1
    Registered User
    Join Date
    07-26-2013
    Location
    Freeholdn NJ
    MS-Off Ver
    Excel 2007
    Posts
    1

    Exclamation Want to match data row on 2 cols of criteria, then display value in 3rd colmun as output

    Hoping someone has done this before - I want to be able to copy paste raw data into a "Data dump" tab (that i will just build upon incrementally 'forever' or reuse over and over after preserving the output data using paste special - values) and have another tab serve as the destination to where the table and formula will live.

    The source data is comprised of 3 values (excerpt below). The idea is that I will trend this data which is received multiple times per day. If I just grab everythign in the report (raw data is in notepad) and copy paste into one tab, how can I have a formula search both the data AND the hour, then once a match is found only pull the value in the 3rd column in? Even if I can get this to work what happens if there are duplicates in that the ame date with same hour but slightly different count in the 3rd column?

    Here is the formula I started with: =SUMIF('Dump Area'!$B$3:$B$26,$B13,'Dump Area'!$C$3:$C$26)

    It works, but only if I group pdays separately (thereby only matching on the 'hour' not the date.

    I've seen references to SUMIFS, VLOOKUP, INDEX, and a few others but none seem to help.

    What am I doing wrong?

    Disc_Date Disc_Hr CallCnt
    2013-07-25 10 425
    2013-07-25 11 479
    2013-07-25 12 629
    2013-07-25 13 868
    2013-07-25 14 1127
    2013-07-25 15 1458
    2013-07-25 16 1941
    2013-07-25 17 1496
    2013-07-25 18 1086
    2013-07-25 19 660
    2013-07-25 20 584
    2013-07-25 21 484
    2013-07-25 22 429
    2013-07-25 23 566
    2013-07-25 08 323
    2013-07-25 09 400
    2013-07-26 00 583
    2013-07-26 01 597
    2013-07-26 10 209
    2013-07-26 11 241
    2013-07-26 12 410
    2013-07-26 13 380
    2013-07-26 14 479
    2013-07-26 02 599
    2013-07-26 03 411
    2013-07-26 04 250
    2013-07-26 05 209
    2013-07-26 06 161
    2013-07-26 07 178
    2013-07-26 08 180
    2013-07-26 09 176

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Want to match data row on 2 cols of criteria, then display value in 3rd colmun as outp

    =sum(if(daterange=date,if(hourrange=hour,callrange))) and entered as an array using Control + Shift + Enter, instead of just Enter.

    =sum(if(A1:A500="2013-07-26",if(b1:b500=10,C1:C500))) this will out put the sum of all values for column C where the rows also match 2013-07-26 and hour 10

    Sumproduct might even do it better, but I'm a sumprod novice.

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Want to match data row on 2 cols of criteria, then display value in 3rd colmun as outp

    Here's the sum(if version.

    daffodil example for jamescairns.xlsx


    And here is the SumProduct version, which is NOT an array:

    Formula: copy to clipboard
    =SUMPRODUCT((datadump!$A$2:$A$32=extraction!A2)*(datadump!$B$2:$B$32=extraction!B2)*(datadump!$C$2:$C$32))



    If column A and B values repeat for any given C, the C values will be added up.

    Meaning the data below would generate a 16 for date 7/25/2013, hour 10.
    7/25/2013 10 10
    7/25/2013 10 6


    A Vlookup however, could circumnavigate the issue of duplicates (if that's what you're aiming for) in that it will choose the first exact match it finds, and not add.
    Last edited by daffodil11; 07-26-2013 at 05:42 PM. Reason: vlookup workaround

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,047

    Re: Want to match data row on 2 cols of criteria, then display value in 3rd colmun as outp

    If there is a duplicate/s, what do you want to return?

    1 option maybe to use a helper column and add the hour/24 to the date...7/25/2013+(10/24), then use index/match to find the value based on that combination?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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] In match search how to display "NO DATA" if no instance of criteria
    By nigelog in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-22-2013, 11:07 AM
  2. Replies: 1
    Last Post: 09-05-2012, 11:39 AM
  3. [SOLVED] Multiple Criteria to produce one output (Match and Vlookup)
    By aglopez08 in forum Excel General
    Replies: 2
    Last Post: 08-21-2012, 04:59 PM
  4. Provide output of Multiple criteria match (II)
    By mromano in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-23-2011, 04:41 PM
  5. Provide output of Multiple criteria match
    By mromano in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-31-2011, 11:40 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