Jan 10 2018
02:29 PM
- last edited on
Jul 25 2018
10:43 AM
by
TechCommunityAP
Jan 10 2018
02:29 PM
- last edited on
Jul 25 2018
10:43 AM
by
TechCommunityAP
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!
Jan 10 2018 02:37 PM
Hi Bianca,
You may try like
=SUMPRODUCT((name=xyz)*(date>=datestart)*(date<=dateend)*(term=xyz)*(D1:D1000))
Jan 10 2018 03:02 PM
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))
Jan 10 2018 03:25 PM
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)?
Jan 11 2018 07:53 AM
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))
Jan 12 2018 02:10 AM
Hi Bianca,
When perhaps that shall be
...*('Term Goals Data2'!I:I="2_Fall 2017")*...