Forum Discussion

Bianca Gordon's avatar
Bianca Gordon
Copper Contributor
Jan 10, 2018

Extracting From Raw Data Sheet

Hi, 

 

There are 4 columns from my raw data sheet that i need to extract data from. 


Here's what I'm looking to create.  

 

From the Raw Data sheet, If Column A(Name) is xyz AND Column B (inquiry date) is between a date range (i.e.,12/31/2017 – 1/1/2018) AND Column C (Start Term) is xyz, then sum Column D (# of Leads) for that date range. 

 

Thanks for your help!

Formulas & Functions
Need Help

5 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jan 10, 2018

    Hi Bianca,

     

    You may try like

    =SUMPRODUCT((name=xyz)*(date>=datestart)*(date<=dateend)*(term=xyz)*(D1:D1000))

     

    • Bianca Gordon's avatar
      Bianca Gordon
      Copper Contributor
      Jan 10, 2018

      Thanks Sergei, 

       

      I am getting an formula error. See below. Let me know if you need snapshots of anything. 

       

      Thanks! 

       

      =SUMPRODUCT(('Term Goals Data2'!U:U=Sheet4!A3)*( 'Term Goals Data2'!Z:Z<=6/18/2017)*( 'Term Goals Data2'!Z:Z<=6/24/2017)*('Term Goals Data2'!I:I=2_Fall 2017)*('Term Goals Data2'!W:W))

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jan 10, 2018

        Bianka,

         

        Comparing with the date doesn't work such way as

        ...'Term Goals Data2'!Z:Z<=6/24/2017

        Most reliable solution is to use some cells where you have your date range. That's easier and much more flexible - sooner or later you'd like to change the dates. If above date in A1 when

        ...'Term Goals Data2'!Z:Z<=$A$1

        If you still prefer the constant when DATEVALUE on text in your locale format, or practical equivalent

        ...'Term Goals Data2'!Z:Z<=("6/24/2017"+0)

        Another point you have two date criteria with AND condition - dates are less than June 18 AND less than June 24. Perhaps it shall be like

        ...*( 'Term Goals Data2'!Z:Z>=("6/18/2017"+0)*( 'Term Goals Data2'!Z:Z<=("6/24/2017"+0))*...

        And what is 2_Fall 2017? Is that defined for some cell name?

         

        What kind of error do you have (#REF!, etc)?

Resources