is there an easier/faster way to do what I'm doing in 3 steps

[Guest]

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

 

[RagDyer]

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

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

" A mistake is not a mistake, but a mistake well made, if we learn
from it".

 

[Guest]

I can't get the syntax correct
Thanks...

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

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

" A mistake is not a mistake, but a mistake well made, if we learn
from it".

 

[Guest]

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

---

 

[Guest]

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

 

[Ron Coderre]

Try something like this:

=SUMPRODUCT((--TEXT(C2:C10,"yyyymm")={200708,200709})*(A2:A10="finance")*(B2:B10="nc"))

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Roger Govier]

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.

 

[Roger Govier]

That's a much neater solution for the OP, Ron.

 

[Ron Coderre]

Thanks, Roger
--------------------------

Best Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

Roger,
Works great thanks.

Ralph

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.

 

[Guest]

Ron,
This works great...Now I have learned 2 ways to perform the same function

Thanks,
Ralph

 

[Ron Coderre]

I'm glad I could help, Ralph......Thanks for letting me know.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)