VBA- Excel formula not working in vba

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
Office Version
  1. 2010
Platform
  1. Windows
Hi Team


Need two help here,
First Help,


I am pasting Below excel formula into vba, its not working. it works in excel.

'=SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$18,{"A","B"},0))*ISNUMBER(MATCH($B$2:$B$18,{"X","Y","Z"},0)))
'=SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$18,$D$4:$D$7,0))*ISNUMBER(MATCH($B$2:$B$18,$E$4:$E$7,0)))


Sub TEST()
ActiveSheet.Range("h1").Formula = "=SUMPRODUCT(ISNUMBER(MATCH(range($A$2:$A$18),{""A"",""B""},0))*ISNUMBER(MATCH(range($B$2:$B$18),{""X"",""Y"",""Z""},0)))"
End Sub


Output formula in H1
=SUMPRODUCT(ISNUMBER(MATCH(range($A$2:$A$18),{"A","B"},0))*ISNUMBER(MATCH(range($B$2:$B$18),{"X","Y","Z"},0)))




Second Help,
If I want the result which are not available in the Criteria Array. how to use <> here.





Thanks for your help in Advance!



Regards,
mg
 
Your method is not going to work for a criteria of greater than, try using the formula

=SUMPRODUCT(COUNTIFS($A$1:$A$36,$E$2:$E$4,$B$1:$B$36,TRANSPOSE($F$2:$F$4),$C$1:$C$36,">="&$G$2))

You can enter it into the sheet with vba using the same methods as before, although you might need to use .FormulaArray instead of .Formula
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi Jason,


Thanks once again for your help.
I tried using suggested formula , Getting result True, but not number Count


Column C contains Numbers, Criteria for it is in G2,which contain 35.
Range("h18").FormulaArray = Evaluate("=SUMPRODUCT(COUNTIFS($A$1:$A$36,$E$2:$E$4,$B$1:$B$36,TRANSPOSE($F$2:$F$4),$C$1:$C$36," >= "&$G$2))")
We are not getting result is it, because we are transposing range (f2:f4), that was the criteria for Column B.


Plz Assist Thanks.




Regards,
mg
 
Last edited:
Upvote 0
You forgot to double up on the double inverted commas in the formula,

Code:
"=SUMPRODUCT(COUNTIFS($A$1:$A$36,$E$2:$E$4,$B$1:$B$36,TRANSPOSE($F$2:$F$4),$C$1:$C$36,"" >= ""&$G$2))"

If you're going to use Evaluate, then use Range("h18").Value, not .FormulaArray
 
Upvote 0
Hi Jason,


Thanks your suggested formula working, But when and why we use transpose function here,
you are transposing Second Criteria here.


Formula works :=> Range("h18").FormulaArray = "=SUMPRODUCT(COUNTIFS($A$1:$A$36,$E$2:$E$4,$B$1:$B$36,TRANSPOSE($F$2:$F$4),$C$1:$C$36, "">=""&$G$2))"


Regards,
mg
 
Upvote 0
If you don't transpose one of the criteria then it would only count the results where

A1:A36=E2 and B1:B36=F2
A1:A36=E3 and B1:B36=F3
A1:A36=E4 and B1:B36=F4

The other 6 possible combinations would not be counted.

Note that you can not have arrays in more that 2 criteria.
 
Upvote 0
Hi Jason,

Thanks for your help and sharing tips with the formula.

(y)

Regards,
mg
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top