VBA - Application.WorksheetFunction

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Hi
Is is possible to use an Application.WorksheetFunction to count depending on
multiple conditions.

This achieves what I'm after in Excel, but I'm not sure how to translate it
to a WorksheetFunction
=SUMPRODUCT((rangeName1="Sumit1")*(rangeName2="Sumit2"))

Thanks
Ben
 
try
=SUMPRODUCT((rangeName1="Sumit1")*(rangeName2="Sumit2"))
Click to expand...

=application.SUMPRODUCT((range("rangeName1")="Sumit1")*(range("rangeName2")=
"Sumit2"))
or
=application.SUMPRODUCT(([rangeName1]="Sumit1")*([rangeName2]="Sumit2"))
 
Hi Don
I get the error code 13 if I try this. I thought that you can't use
SUMPRODUCT in VBA in combination with condition checks?

But maybe I'm missing something here :-)


--
Regards
Frank Kabel
Frankfurt, Germany

Don said:
try
=SUMPRODUCT((rangeName1="Sumit1")*(rangeName2="Sumit2"))
Click to expand...
=application.SUMPRODUCT((range("rangeName1")="Sumit1")*(range("rangeNam
e2")=
"Sumit2"))
or
Click to expand...
=application.SUMPRODUCT(([rangeName1]="Sumit1")*([rangeName2]="Sumit2")
)
Hi
Is is possible to use an Application.WorksheetFunction to count
depending on multiple conditions.

This achieves what I'm after in Excel, but I'm not sure how to
translate it to a WorksheetFunction
=SUMPRODUCT((rangeName1="Sumit1")*(rangeName2="Sumit2"))

Thanks
Ben
Click to expand...
Click to expand...
 
Frank, I forgot. This will work to sum col b if col a has "a"

Sub sumpro()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
If Cells(i, "a") = "a" Then mytot = mytot + Cells(i, "a").Offset(, 1)
Next
MsgBox mytot
End Sub

and this will count em

Sub sumprocount()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
If Cells(i, "a") = "a" And Cells(i, "b") = 1 Then mytot = mytot + 1
Next
MsgBox mytot
End Sub
--
Don Guillett
SalesAid Software
(e-mail address removed)
Frank Kabel said:
Hi Don
I get the error code 13 if I try this. I thought that you can't use
SUMPRODUCT in VBA in combination with condition checks?

But maybe I'm missing something here :-)


--
Regards
Frank Kabel
Frankfurt, Germany

Don said:
try
=SUMPRODUCT((rangeName1="Sumit1")*(rangeName2="Sumit2"))
Click to expand...
=application.SUMPRODUCT((range("rangeName1")="Sumit1")*(range("rangeNam
e2")=
"Sumit2"))
or
Click to expand...
=application.SUMPRODUCT(([rangeName1]="Sumit1")*([rangeName2]="Sumit2")
)
Hi
Is is possible to use an Application.WorksheetFunction to count
depending on multiple conditions.

This achieves what I'm after in Excel, but I'm not sure how to
translate it to a WorksheetFunction
=SUMPRODUCT((rangeName1="Sumit1")*(rangeName2="Sumit2"))

Thanks
Ben
Click to expand...
Click to expand...
Click to expand...
 
Hi Don
wasn't sure myself, thanks for clarifying. Maybe this will help the OP
:-)

--
Regards
Frank Kabel
Frankfurt, Germany

Don said:
Frank, I forgot. This will work to sum col b if col a has "a"

Sub sumpro()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
If Cells(i, "a") = "a" Then mytot = mytot + Cells(i, "a").Offset(, 1)
Next
MsgBox mytot
End Sub

and this will count em

Sub sumprocount()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
If Cells(i, "a") = "a" And Cells(i, "b") = 1 Then mytot = mytot + 1
Next
MsgBox mytot
End Sub
Hi Don
I get the error code 13 if I try this. I thought that you can't use
SUMPRODUCT in VBA in combination with condition checks?

But maybe I'm missing something here :-)


--
Regards
Frank Kabel
Frankfurt, Germany

Don said:
try
=SUMPRODUCT((rangeName1="Sumit1")*(rangeName2="Sumit2"))
Click to expand...
=application.SUMPRODUCT((range("rangeName1")="Sumit1")*(range("rangeNam
e2")=
"Sumit2"))
or
Click to expand...
=application.SUMPRODUCT(([rangeName1]="Sumit1")*([rangeName2]="Sumit2")
)
Hi
Is is possible to use an Application.WorksheetFunction to count
depending on multiple conditions.

This achieves what I'm after in Excel, but I'm not sure how to
translate it to a WorksheetFunction
=SUMPRODUCT((rangeName1="Sumit1")*(rangeName2="Sumit2"))

Thanks
Ben
Click to expand...
Click to expand...
Click to expand...
 
I'm guessing that rangeName1 and rangeName2 are real rangenames--not VBA
variables.

If that's true, you could wrap your formula with application.evaluate().

Like:

MsgBox _
Application.Evaluate _
("SUMPRODUCT((rangeName1=""Sumit1"")*(rangeName2=""Sumit2""))")

(continuation characters to stop wrapping in the post.)
 
Just the ticket, thanks (all)!

Dave Peterson said:
I'm guessing that rangeName1 and rangeName2 are real rangenames--not VBA
variables.

If that's true, you could wrap your formula with application.evaluate().

Like:

MsgBox _
Application.Evaluate _
("SUMPRODUCT((rangeName1=""Sumit1"")*(rangeName2=""Sumit2""))")

(continuation characters to stop wrapping in the post.)
Click to expand...
 
Back
Top