Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > is there an easier/faster way to do what I'm doing in 3 steps
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 16
Default 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:
" A mistake is not a mistake, but a mistake well made, if we learn
from it".
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,572
Default 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:
" A mistake is not a mistake, but a mistake well made, if we learn
from it".



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 16
Default 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:
" A mistake is not a mistake, but a mistake well made, if we learn
from it".




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
  
Posts: 9,221
Default 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
---
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 16
Default 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
---
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,118
Default 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
---


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,480
Default 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
---


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,480
Default 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
---




Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,118
Default 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)



Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 16
Default 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
---



Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 16
Default 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
---



Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,118
Default 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
---





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
making steps damorrison Excel Discussion (Misc queries) 1 April 19th 06 01:04 PM
steps to eM URL? [email protected] Excel Discussion (Misc queries) 1 March 5th 06 08:02 PM
steps to display .wmf ? [email protected] Excel Discussion (Misc queries) 1 March 2nd 06 10:27 PM
Can faster CPU+larger/faster RAM significantly speed up recalulati jmk_li Excel Discussion (Misc queries) 2 September 28th 05 10:24 AM
Baby steps..... cpegram Excel Discussion (Misc queries) 5 June 4th 05 09:32 PM


All times are GMT +1. The time now is 02:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement