Sum If in VBA

[Baapi]

=SUMPRODUCT(('Production Log'!$H$5:$H$7308=$C11),('Production
Log'!$K$5:$K$7308=H$8),('Production Log'!$J$5:$J$7308=H$9))

gives a "0"

Whereas,

=SUMPRODUCT(('Production Log'!$H$5:$H$7308=$C11),('Production
Log'!$K$5:$K$7308=H$8),('Production Log'!$J$5:$J$7308=H$9),1)

gives #VALUE

and my original formula
=SUM(IF('Production Log'!$H$5:$H$25000=$C11,IF('Production
Log'!$K$5:$K$25000=H$8,IF('Production Log'!$J$5:$J$25000=H$9,1,0))))

Was working fine. But I want to put this formula into Excel through the
script and this formula requires a CTRL+SHIFT+ENTER

and I gave a "SendKeys ("^+~"), True"

None of these seem to work...

Experts!! What do you have to say?

 

[Bob Phillips]

=SUMPRODUCT(('Production Log'!$H$5:$H$7308=$C11),('Production
Log'!$K$5:$K$7308=H$8),('Production Log'!$J$5:$J$7308=H$9))

gives a "0"

will give if there is no row that meets all conditions.

Whereas,

=SUMPRODUCT(('Production Log'!$H$5:$H$7308=$C11),('Production
Log'!$K$5:$K$7308=H$8),('Production Log'!$J$5:$J$7308=H$9),1)

gives #VALUE

That is syntactically incoorect, You cannot use comma separators with
condtitional tests.

and my original formula
=SUM(IF('Production Log'!$H$5:$H$25000=$C11,IF('Production
Log'!$K$5:$K$25000=H$8,IF('Production Log'!$J$5:$J$25000=H$9,1,0))))

Was working fine. But I want to put this formula into Excel through the
script and this formula requires a CTRL+SHIFT+ENTER

As a formula or a result? Former use Formulaarray, latter, use evaluate.

 

[Guest]

It is tough to debug without knowing what the source data looks like. That
being said you probably want to replace the , with *. Here is a link to a
reference on SumProduct that might help...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Baapi]

This is how my Source Data Looks Like
Date (H) Action (I) LOB (J) Syste
(K)
---------------------------------------------------------------------------
09-Aug-2005 REGISTERED WC AS400C
09-Aug-2005 DUPLICATE WC AS400C
17-Aug-2005 REGISTERED WC AS400C
22-Aug-2005 REGISTERED WC AS400C
06-Sep-2005 REGISTERED WC AS400C
08-Jul-2005 REGISTERED WC AS400C
27-Jul-2005 REGISTERED WC AS400C
27-Jul-2005 REGISTERED WC AS400C

I want to count the number of rows which satisfies all my thre
conditions.
Column H = Specific Date,
Column J = Specific LOB and
Column K = Specific System

This Value, I want to put in a cell as value.
Also, If you could tell me how to put this as a formula...

it would be great.

And Bob, I've seen you using "--" while you use SUMPRODUCT, I've see
someone else using just one of those. and some people not using them a
all. What is this all about?

Thanks.
Baap

 

[Bob Phillips]

This is how my Source Data Looks Like
Date (H) Action (I) LOB (J) System
(K)
-------------------------------------------------------------------------- -
09-Aug-2005 REGISTERED WC AS400C
09-Aug-2005 DUPLICATE WC AS400C
17-Aug-2005 REGISTERED WC AS400C
22-Aug-2005 REGISTERED WC AS400C
06-Sep-2005 REGISTERED WC AS400C
08-Jul-2005 REGISTERED WC AS400C
27-Jul-2005 REGISTERED WC AS400C
27-Jul-2005 REGISTERED WC AS400C

I want to count the number of rows which satisfies all my three
conditions.
Column H = Specific Date,
Column J = Specific LOB and
Column K = Specific System

This Value, I want to put in a cell as value.
Also, If you could tell me how to put this as a formula...

=SUMPRODUCT(--('Production Log'!$H$5:$H$7308=$C11),--('Production
Log'!$K$5:$K$7308=H$8),--('Production Log'!$J$5:$J$7308=H$9))

assuming your values to test against are as in your original posting.

And Bob, I've seen you using "--" while you use SUMPRODUCT, I've seen
someone else using just one of those. and some people not using them at
all. What is this all about?

It is all explained in the link Jim provided
http://www.xldynamic.com/source/xld.SUMPRODUCT.html. Don't use just one
though, as you will have to pair those off, whereas you don't have that
constraint with -- or the * operator.

 

[Guest]

That was great Bob.
Now I have the following formula and is working.

Cells(R, C).Value = Evaluate("SUMPRODUCT(--('Production Log'!$H$5:$H$" &
eRowP & "=C11),--('Production Log'!$K$5:$K$" & eRowP & "=E$8),--('Production
Log'!$J$5:$J$" & eRowP & "=E$9))")

But If I try to replace the conditions (C11, E8 and E9) with Range
Variables, it gives me "FALSE" as result

formula I'm using is this
Set Cr1 = Range("C11") 'C11 actally contains calculated dates
Set Cr2 = Range("E8") ' E8 and E9 caontain text
Set Cr3 = Range("E9")
Cells(R, C).Value = Evaluate("SUMPRODUCT(--('Production Log'!$H$5:$H$" &
eRowP = Cr1 & "),--('Production Log'!$K$5:$K$" & eRowP = Cr2 &
"),--('Production Log'!$J$5:$J$" & eRowP = Cr3 &"))")

 

[Baapi]

Dim Cr1 As Range, Cr2 As Range, Cr3 As Range
Dim RngP1 As Range, RngP2 As Range, RngP3 As Range

eRowP = Worksheets("Production Log").Cells(Rows.Count
1).End(xlUp).Row

Set RngP1 = Range(Cells(5, 8), Cells(eRowP, 8))
Set RngP2 = Range(Cells(5, 10), Cells(eRowP, 10))
Set RngP3 = Range(Cells(5, 11), Cells(eRowP, 11))

Set Cr1 = Range("C11")
Set Cr2 = Range("E9")
Set Cr3 = Range("E8")

Cells(R, C).Value = Evaluate("SUMPRODUCT(--(RngP1 = Cr1),--(RngP2
Cr2),--(RngP3 = Cr3))")

gives me #NAME?

Now, I'm sitting on this for the past two days...

Can someone please help resolve this NOW!!

 

[Baapi]

Dim Cr1 As Range, Cr2 As Range, Cr3 As Range
Dim RngP1 As Range, RngP2 As Range, RngP3 As Range

eRowP = Worksheets("Production Log").Cells(Rows.Count,
1).End(xlUp).Row

Set RngP1 = Range(Cells(5, 8), Cells(eRowP, 8))
Set RngP2 = Range(Cells(5, 10), Cells(eRowP, 10))
Set RngP3 = Range(Cells(5, 11), Cells(eRowP, 11))

Set Cr1 = Range("C11")
Set Cr2 = Range("E9")
Set Cr3 = Range("E8")

Cells(R, C).Value = Evaluate("SUMPRODUCT(--(RngP1 = Cr1),--(RngP2 =
Cr2),--(RngP3 = Cr3))")

gives me #NAME?

Now, I'm sitting on this for the past two days...

Can someone please help resolve this NOW!!!

 

[Guest]

--
Baapi