|
is there an easier/faster way to do what I'm doing in 3 steps
Is there an easy way to combine the following 2 equations:
Currently I"m getting my result in 3 steps, I'd like to do it in 1 cell (formula), step 1: =SUMPRODUCT(--('Filled Reqs-Aug'!$R$2:$R$389=DATE(2007,8,1)),--('Filled Reqs-Aug'!$R$2:$R$389<=DATE(2007,8,31)),--('Filled Reqs-Aug'!$B$2:$B$389="finance"),--('Filled Reqs-Aug'!$I$2:$I$389="NC")) cell A2 4 step 2: =SUMPRODUCT(--('Filled Reqs-Sep'!$S$2:$S$389=DATE(2007,9,1)),--('Filled Reqs-Sep'!$S$2:$S$389<=DATE(2007,9,30)),--('Filled Reqs-Sep'!$B$2:$B$389="finance"),--('Filled Reqs-Sep'!$I$2:$I$389="NC")) cell C2 6 step 3: =sum(A2:C2) cell D2 10 Result cell D2 must be 10 Thanks in advance Ralph Quote:
|
is there an easier/faster way to do what I'm doing in 3 steps
Probably the easiest way is to join both formulas with a + sign.
-- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ralph D''Andrea" wrote in message ... Is there an easy way to combine the following 2 equations: Currently I"m getting my result in 3 steps, I'd like to do it in 1 cell (formula), step 1: =SUMPRODUCT(--('Filled Reqs-Aug'!$R$2:$R$389=DATE(2007,8,1)),--('Filled Reqs-Aug'!$R$2:$R$389<=DATE(2007,8,31)),--('Filled Reqs-Aug'!$B$2:$B$389="finance"),--('Filled Reqs-Aug'!$I$2:$I$389="NC")) cell A2 4 step 2: =SUMPRODUCT(--('Filled Reqs-Sep'!$S$2:$S$389=DATE(2007,9,1)),--('Filled Reqs-Sep'!$S$2:$S$389<=DATE(2007,9,30)),--('Filled Reqs-Sep'!$B$2:$B$389="finance"),--('Filled Reqs-Sep'!$I$2:$I$389="NC")) cell C2 6 step 3: =sum(A2:C2) cell D2 10 Result cell D2 must be 10 Thanks in advance Ralph Quote:
|
is there an easier/faster way to do what I'm doing in 3 steps
I can't get the syntax correct
Thanks... "RagDyer" wrote: Probably the easiest way is to join both formulas with a + sign. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ralph D''Andrea" wrote in message ... Is there an easy way to combine the following 2 equations: Currently I"m getting my result in 3 steps, I'd like to do it in 1 cell (formula), step 1: =SUMPRODUCT(--('Filled Reqs-Aug'!$R$2:$R$389=DATE(2007,8,1)),--('Filled Reqs-Aug'!$R$2:$R$389<=DATE(2007,8,31)),--('Filled Reqs-Aug'!$B$2:$B$389="finance"),--('Filled Reqs-Aug'!$I$2:$I$389="NC")) cell A2 4 step 2: =SUMPRODUCT(--('Filled Reqs-Sep'!$S$2:$S$389=DATE(2007,9,1)),--('Filled Reqs-Sep'!$S$2:$S$389<=DATE(2007,9,30)),--('Filled Reqs-Sep'!$B$2:$B$389="finance"),--('Filled Reqs-Sep'!$I$2:$I$389="NC")) cell C2 6 step 3: =sum(A2:C2) cell D2 10 Result cell D2 must be 10 Thanks in advance Ralph Quote:
|
is there an easier/faster way to do what I'm doing in 3 steps
"Ralph D''Andrea" wrote:
I can't get the syntax correct .. Believe the suggestion was simply to collapse the 2 sumproducts (in A2 and C2) into a single cell in D2, like this: sumproduct(...)+sumproduct(...) Or, you could also collapse it in D2 using SUM, eg: =sum(sumproduct(...),sumproduct(...)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
is there an easier/faster way to do what I'm doing in 3 steps
heres my data:
A B C finance nc 8/3/2007 * legal vp 7/30/2007 gto nc 4/10/1902 gtb avp 8/31/2007 finance nc 9/5/2007 * finance nc 9/12/2007 * ies assoc 8/24/2007 am nc 8/25/2007 tax nc 9/30/2007 HERE'S MY FORMULA: =SUMPRODUCT(--(C2:C10=DATE(2007,9,1)),--(C2:C10<=DATE(2007,9,30)),--(A2:A10="finance"),--(B2:B10="NC")+SUMPRODUCT(--(C2:C10=DATE(2007,8,1)),--(C2:C10<=DATE(2007,8,31)),--(A2:A10="finance"),--(B2:B10="NC"))) I GET 4, THE ANSWER SHOULD BE 3 Thanks, Ralph "Max" wrote: "Ralph D''Andrea" wrote: I can't get the syntax correct .. Believe the suggestion was simply to collapse the 2 sumproducts (in A2 and C2) into a single cell in D2, like this: sumproduct(...)+sumproduct(...) Or, you could also collapse it in D2 using SUM, eg: =sum(sumproduct(...),sumproduct(...)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
is there an easier/faster way to do what I'm doing in 3 steps
Try something like this:
=SUMPRODUCT((--TEXT(C2:C10,"yyyymm")={200708,200709})*(A2:A10="fi nance")*(B2:B10="nc")) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ralph D''Andrea" wrote in message ... heres my data: A B C finance nc 8/3/2007 * legal vp 7/30/2007 gto nc 4/10/1902 gtb avp 8/31/2007 finance nc 9/5/2007 * finance nc 9/12/2007 * ies assoc 8/24/2007 am nc 8/25/2007 tax nc 9/30/2007 HERE'S MY FORMULA: =SUMPRODUCT(--(C2:C10=DATE(2007,9,1)),--(C2:C10<=DATE(2007,9,30)),--(A2:A10="finance"),--(B2:B10="NC")+SUMPRODUCT(--(C2:C10=DATE(2007,8,1)),--(C2:C10<=DATE(2007,8,31)),--(A2:A10="finance"),--(B2:B10="NC"))) I GET 4, THE ANSWER SHOULD BE 3 Thanks, Ralph "Max" wrote: "Ralph D''Andrea" wrote: I can't get the syntax correct .. Believe the suggestion was simply to collapse the 2 sumproducts (in A2 and C2) into a single cell in D2, like this: sumproduct(...)+sumproduct(...) Or, you could also collapse it in D2 using SUM, eg: =sum(sumproduct(...),sumproduct(...)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
is there an easier/faster way to do what I'm doing in 3 steps
Hi Ralph
The formula should be =SUMPRODUCT(--(C3:C11=DATE(2007,9,1)),--(C3:C11<=DATE(2007,9,30)), --(A3:A11="finance"),--(B3:B11="NC")) +SUMPRODUCT(--(C3:C11=DATE(2007,8,1)),--(C3:C11<=DATE(2007,8,31)), --(A3:A11="finance"),--(B3:B11="NC")) The first Sumproduct formula needs terminating with it's closing parenthesis, before having the "+" sign and then the next complete Sumproduct formula. You had them both within the overall set of Sumproduct parentheses. -- Regards Roger Govier "Ralph D''Andrea" wrote in message ... heres my data: A B C finance nc 8/3/2007 * legal vp 7/30/2007 gto nc 4/10/1902 gtb avp 8/31/2007 finance nc 9/5/2007 * finance nc 9/12/2007 * ies assoc 8/24/2007 am nc 8/25/2007 tax nc 9/30/2007 HERE'S MY FORMULA: =SUMPRODUCT(--(C2:C10=DATE(2007,9,1)),--(C2:C10<=DATE(2007,9,30)),--(A2:A10="finance"),--(B2:B10="NC")+SUMPRODUCT(--(C2:C10=DATE(2007,8,1)),--(C2:C10<=DATE(2007,8,31)),--(A2:A10="finance"),--(B2:B10="NC"))) I GET 4, THE ANSWER SHOULD BE 3 Thanks, Ralph "Max" wrote: "Ralph D''Andrea" wrote: I can't get the syntax correct .. Believe the suggestion was simply to collapse the 2 sumproducts (in A2 and C2) into a single cell in D2, like this: sumproduct(...)+sumproduct(...) Or, you could also collapse it in D2 using SUM, eg: =sum(sumproduct(...),sumproduct(...)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
is there an easier/faster way to do what I'm doing in 3 steps
That's a much neater solution for the OP, Ron.
-- Regards Roger Govier "Ron Coderre" wrote in message ... Try something like this: =SUMPRODUCT((--TEXT(C2:C10,"yyyymm")={200708,200709})*(A2:A10="fi nance")*(B2:B10="nc")) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ralph D''Andrea" wrote in message ... heres my data: A B C finance nc 8/3/2007 * legal vp 7/30/2007 gto nc 4/10/1902 gtb avp 8/31/2007 finance nc 9/5/2007 * finance nc 9/12/2007 * ies assoc 8/24/2007 am nc 8/25/2007 tax nc 9/30/2007 HERE'S MY FORMULA: =SUMPRODUCT(--(C2:C10=DATE(2007,9,1)),--(C2:C10<=DATE(2007,9,30)),--(A2:A10="finance"),--(B2:B10="NC")+SUMPRODUCT(--(C2:C10=DATE(2007,8,1)),--(C2:C10<=DATE(2007,8,31)),--(A2:A10="finance"),--(B2:B10="NC"))) I GET 4, THE ANSWER SHOULD BE 3 Thanks, Ralph "Max" wrote: "Ralph D''Andrea" wrote: I can't get the syntax correct .. Believe the suggestion was simply to collapse the 2 sumproducts (in A2 and C2) into a single cell in D2, like this: sumproduct(...)+sumproduct(...) Or, you could also collapse it in D2 using SUM, eg: =sum(sumproduct(...),sumproduct(...)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
is there an easier/faster way to do what I'm doing in 3 steps
Thanks, Roger
-------------------------- Best Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... That's a much neater solution for the OP, Ron. -- Regards Roger Govier "Ron Coderre" wrote in message ... Try something like this: =SUMPRODUCT((--TEXT(C2:C10,"yyyymm")={200708,200709})*(A2:A10="fi nance")*(B2:B10="nc")) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) |
is there an easier/faster way to do what I'm doing in 3 steps
Roger,
Works great thanks. Ralph "Roger Govier" wrote: Hi Ralph The formula should be =SUMPRODUCT(--(C3:C11=DATE(2007,9,1)),--(C3:C11<=DATE(2007,9,30)), --(A3:A11="finance"),--(B3:B11="NC")) +SUMPRODUCT(--(C3:C11=DATE(2007,8,1)),--(C3:C11<=DATE(2007,8,31)), --(A3:A11="finance"),--(B3:B11="NC")) The first Sumproduct formula needs terminating with it's closing parenthesis, before having the "+" sign and then the next complete Sumproduct formula. You had them both within the overall set of Sumproduct parentheses. -- Regards Roger Govier "Ralph D''Andrea" wrote in message ... heres my data: A B C finance nc 8/3/2007 * legal vp 7/30/2007 gto nc 4/10/1902 gtb avp 8/31/2007 finance nc 9/5/2007 * finance nc 9/12/2007 * ies assoc 8/24/2007 am nc 8/25/2007 tax nc 9/30/2007 HERE'S MY FORMULA: =SUMPRODUCT(--(C2:C10=DATE(2007,9,1)),--(C2:C10<=DATE(2007,9,30)),--(A2:A10="finance"),--(B2:B10="NC")+SUMPRODUCT(--(C2:C10=DATE(2007,8,1)),--(C2:C10<=DATE(2007,8,31)),--(A2:A10="finance"),--(B2:B10="NC"))) I GET 4, THE ANSWER SHOULD BE 3 Thanks, Ralph "Max" wrote: "Ralph D''Andrea" wrote: I can't get the syntax correct .. Believe the suggestion was simply to collapse the 2 sumproducts (in A2 and C2) into a single cell in D2, like this: sumproduct(...)+sumproduct(...) Or, you could also collapse it in D2 using SUM, eg: =sum(sumproduct(...),sumproduct(...)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
is there an easier/faster way to do what I'm doing in 3 steps
Ron,
This works great...Now I have learned 2 ways to perform the same function Thanks, Ralph "Ron Coderre" wrote: Try something like this: =SUMPRODUCT((--TEXT(C2:C10,"yyyymm")={200708,200709})*(A2:A10="fi nance")*(B2:B10="nc")) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ralph D''Andrea" wrote in message ... heres my data: A B C finance nc 8/3/2007 * legal vp 7/30/2007 gto nc 4/10/1902 gtb avp 8/31/2007 finance nc 9/5/2007 * finance nc 9/12/2007 * ies assoc 8/24/2007 am nc 8/25/2007 tax nc 9/30/2007 HERE'S MY FORMULA: =SUMPRODUCT(--(C2:C10=DATE(2007,9,1)),--(C2:C10<=DATE(2007,9,30)),--(A2:A10="finance"),--(B2:B10="NC")+SUMPRODUCT(--(C2:C10=DATE(2007,8,1)),--(C2:C10<=DATE(2007,8,31)),--(A2:A10="finance"),--(B2:B10="NC"))) I GET 4, THE ANSWER SHOULD BE 3 Thanks, Ralph "Max" wrote: "Ralph D''Andrea" wrote: I can't get the syntax correct .. Believe the suggestion was simply to collapse the 2 sumproducts (in A2 and C2) into a single cell in D2, like this: sumproduct(...)+sumproduct(...) Or, you could also collapse it in D2 using SUM, eg: =sum(sumproduct(...),sumproduct(...)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
is there an easier/faster way to do what I'm doing in 3 steps
I'm glad I could help, Ralph......Thanks for letting me know.
-------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ralph D''Andrea" wrote in message ... Ron, This works great...Now I have learned 2 ways to perform the same function Thanks, Ralph "Ron Coderre" wrote: Try something like this: =SUMPRODUCT((--TEXT(C2:C10,"yyyymm")={200708,200709})*(A2:A10="fi nance")*(B2:B10="nc")) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ralph D''Andrea" wrote in message ... heres my data: A B C finance nc 8/3/2007 * legal vp 7/30/2007 gto nc 4/10/1902 gtb avp 8/31/2007 finance nc 9/5/2007 * finance nc 9/12/2007 * ies assoc 8/24/2007 am nc 8/25/2007 tax nc 9/30/2007 HERE'S MY FORMULA: =SUMPRODUCT(--(C2:C10=DATE(2007,9,1)),--(C2:C10<=DATE(2007,9,30)),--(A2:A10="finance"),--(B2:B10="NC")+SUMPRODUCT(--(C2:C10=DATE(2007,8,1)),--(C2:C10<=DATE(2007,8,31)),--(A2:A10="finance"),--(B2:B10="NC"))) I GET 4, THE ANSWER SHOULD BE 3 Thanks, Ralph "Max" wrote: "Ralph D''Andrea" wrote: I can't get the syntax correct .. Believe the suggestion was simply to collapse the 2 sumproducts (in A2 and C2) into a single cell in D2, like this: sumproduct(...)+sumproduct(...) Or, you could also collapse it in D2 using SUM, eg: =sum(sumproduct(...),sumproduct(...)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| All times are GMT +1. The time now is 07:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com