Array Formula in VBA
 

Hi!VBA expert,

I need some help in coding the formula below.

I have the below code in a module. The first part is working for co
“L”.
result = 0
result = Application.WorksheetFunction.Sum(Range("A" & myRow, "J"
myRow))
Cells(myRow, 12).Value = result

In col “M” I like to add in the below formula, but don’t know how t
code it in VBA.

Array Formula = SUM(IF(A1:G1=100,IF(A1:G1<=199.99,1,0)))

Can someone tell me how to do it?

Thanks,
Michae

--
Message posted from http://www.ExcelForum.com

Array Formula in VBA
 

If you want to count the number of cells that meet the 100 to 20
criteria,
=COUNTIF(A1:G1,"=100")-COUNTIF(A1:G1,"200")

If you want to add the numbers,
=SUMIF(A1:G1,"=100")-SUMIF(A1:G1,"200")

-gitcyphe

--
Message posted from http://www.ExcelForum.com

Array Formula in VBA
 

Hi!gitcypher,

Thanks for the answer.

Regards,
Michael

gitcypher wrote:
*If you want to count the number of cells that meet the 100 to 20
criteria,
application.worksheetfunction.COUNTIF(A1:G1,"=100 ")-application.worksheetfunction.COUNTIF(A1:G1,"200" )

If you want to add the numbers,
application.worksheetfunction.SUMIF(A1:G1,"=100")-application.worksheetfunction.SUMIF(A1:G1,"200")

-gitcypher

--
Message posted from http://www.ExcelForum.com