VBA- Excel formula not working in vba

[Mallesh23]

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

 

[jasonb75]

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

 

[Mallesh23]

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

 

[jasonb75]

You forgot to double up on the double inverted commas in 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))"

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

 

[Mallesh23]

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

 

[jasonb75]

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.

 

[Mallesh23]

Hi Jason,

Thanks for your help and sharing tips with the formula.



Regards,
mg