Extracting From Raw Data Sheet

[Bianca Gordon]

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!

[#2]

Hi Bianca,

You may try like

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

[#3]

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))

[#4]

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)?

[#5]

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))

[#6]

Hi Bianca,

When perhaps that shall be

...*('Term Goals Data2'!I:I="2_Fall 2017")*...