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 ...
Formulas & Functions
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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
      Diamond Contributor
      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)?

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

        Hi Sergei, 

         

        Here's the updated formula and the error I'm still getting is that there is a problem with the formula. 

        2_Fall 2017 is a name under column Term. 

         

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

Resources