How to use SUMPRODUCT in macro?

[Tarek]

Dear all,

Now I m just testing and learned about SUMPRODUCT parameter of excel.
But actually, I need to translate and applicate in macro(i need the syntax).
So I can't solve it now!

Anyone can help me and feel greatly appreciate !

--
Regards,
Tarek ^^'

-----------------------
Tarek's WorkShop

[Bob Phillips]

Easiset way

Debug.Print
Evaluate("SUMPRODUCT(--(A1:A10=""abc""),--(B1:B10=17),C1:C10)")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tarek" <[email protected]> wrote in message
news:[email protected]...
> Dear all,
>
> Now I m just testing and learned about SUMPRODUCT parameter of excel.
> But actually, I need to translate and applicate in macro(i need the
syntax).
> So I can't solve it now!
>
> Anyone can help me and feel greatly appreciate !
>
> --
> Regards,
> Tarek ^^'
>
> -----------------------
> Tarek's WorkShop

[Cora]

Dear You,

Is use this to put SUMPRODUCT in Vba's Macro :
ActiveSheet.Range("A1").Select
ActiveCell.FormulaR1C1Local = "=SUMPRODUCT(('C1:C10 = 1)*(D1:D10 =
"Ya"))"

And if you put only the result on the sheet:
ActiveCell.Value = ActiveCell.Value


"=?Utf-8?B?VGFyZWs=?=" <[email protected]> wrote in
news:[email protected]:

> Dear all,
>
> Now I m just testing and learned about SUMPRODUCT parameter of
> excel.
> But actually, I need to translate and applicate in macro(i need the
> syntax). So I can't solve it now!
>
> Anyone can help me and feel greatly appreciate !
>

[Bob Phillips]

"Cora" <[email protected]> wrote in message
news:[email protected]...

> Is use this to put SUMPRODUCT in Vba's Macro :
> ActiveSheet.Range("A1").Select
> ActiveCell.FormulaR1C1Local = "=SUMPRODUCT(('C1:C10 = 1)*(D1:D10 =
> "Ya"))"

I think you must mean

ActiveCell.Formula = "=SUMPRODUCT((C1:C10 = 1)*(D1:D10 = ""Ya""))"

[Cora]

"Bob Phillips" <[email protected]> wrote in news:e7d9J
[email protected]:

> ActiveCell.FormulaR1C1Local = "=SUMPRODUCT(('C1:C10 = 1)*(D1:D10 =
>> "Ya"))"
>

No

ActiveCell.Formula = "=SUMPRODUCT((C1:C10 = 1)*(D1:D10 = ""Ya""))"

Didn't work (#Name? appears in cell)

ActiveCell.FormulaR1C1Local = "=SUMPRODUCT((C1:C10 = 1)*(D1:D10 = ""Ya""))"

Give the good result

[Bob Phillips]

Odd.

Can you try this for me?

ActiveCell.Formula = "=SOMMEPROD((C1:C10 = 1)*(D1:D10 = ""Ya""))"



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Cora" <[email protected]> wrote in message
news:[email protected]...
> "Bob Phillips" <[email protected]> wrote in news:e7d9J
> [email protected]:
>
> > ActiveCell.FormulaR1C1Local = "=SUMPRODUCT(('C1:C10 = 1)*(D1:D10 =
> >> "Ya"))"
> >
>
> No
>
> ActiveCell.Formula = "=SUMPRODUCT((C1:C10 = 1)*(D1:D10 = ""Ya""))"
>
> Didn't work (#Name? appears in cell)
>
> ActiveCell.FormulaR1C1Local = "=SUMPRODUCT((C1:C10 = 1)*(D1:D10 =
""Ya""))"
>
> Give the good result

[Cora]

"Bob Phillips" <[email protected]> wrote in
news:[email protected]:

> Odd.
>
> Can you try this for me?
>
> ActiveCell.Formula = "=SOMMEPROD((C1:C10 = 1)*(D1:D10 = ""Ya""))"
>
>
>

Hello,

I've already try this formula. The result appear only when I edit cell with
F2 and Enter (I refer cells in an another workbook who is open in the same
macro).

When I use FormulaR1C1Local the result appear immediately.

bye