Sumproduct & sumifs formula in vba code

[RAM72]

Hi All

I just learning but I am stuck with sumproduct and sumifs formula in vba
I create a button to calculate invoices from column A invoices, I used a macro but i would prefer it in vba

Is it possible to have the correct vba code for sumproduct and sumifs .

Does sumifs vba code work on excel 2002 and 2003 version.
I need to test on old version of excel

So far sumproduct is bringing me issues !!!1

Thanks if someone can help.
Macro recorder for sumproduct

' InvoiceAmtcalculation Macro

' Invoice calculation per invoices issued
'
'
  ActiveWindow.ScrollColumn = 11
  ActiveWindow.ScrollColumn = 12
  ActiveWindow.ScrollColumn = 13
  ActiveWindow.ScrollColumn = 14
  ActiveCell.FormulaR1C1 = "=SUMPRODUCT(R2C17:R410C17,--(R2C1:R410C1=RC[-1]))"
  Range("AB2").Select
  Selection.AutoFill Destination:=Range("AB2:AB36")
  Range("AB2:AB36").Select
  ActiveWindow.SmallScroll Down:=27
End Sub

macrorecorder for sumifs

Sub SUMIFINVOICESCALCULATION()
'
' SUMIFINVOICESCALCULATION Macro
' CALCULATE INVOICES SEPARATELY
'

'
  Range("AB2").Select
  ActiveCell.FormulaR1C1 = "=SUMIFS(R2C17:R410C17,R2C1:R410C1,RC[-1])"
  Range("AB2").Select
  Selection.AutoFill Destination:=Range("AB2:AB36")
  Range("AB2:AB36").Select
End Sub

Mod Edit: Code Tags added

 

[Marc L]

Hi !​

Does sumifs vba code work on excel 2002 and 2003 version.

All whateverIFS Excel worksheet functions do not exist under 2007 version …
​

 

[RAM72]

Hi !​

All whateverIFS Excel worksheet functions do not exist under 2007 version …
​

ok Marc, at least I am fixed, I will need of a vba code for sumproduct

 

[Debaser]

The code is just:

    Range("AB2:AB36").FormulaR1C1 = "=SUMPRODUCT(R2C17:R410C17,--(R2C1:R410C1=RC[-1]))"

 

[RAM72]

Check ok, however do you know how to make the formula not appear in the cells .I do not want users to mess with formulas.
Just click and have results

 

[Debaser]

You can replace with the values:

With Range("AB2:AB36")
   .FormulaR1C1 = "=SUMPRODUCT(R2C17:R410C17,--(R2C1:R410C1=RC[-1]))"
   .Value2 = .Value2
End With

 

[RAM72]

Thanks you

How to set range AB2 to last data in column and sumproduct to last data in row in the above in vb

 

[Debaser]

I guess you mean something like:

With Range("AB2:AB" & Cells(Rows.Count, "AA").End(xlUp).Row)
   .FormulaR1C1 = "=SUMPRODUCT(R2C17:R410C17,--(R2C1:R410C1=RC[-1]))"
   .Value2 = .Value2
End With

 

[RAM72]

Thank you for your help

 

[Marc L]

All whateverIFS Excel worksheet functions do not exist under 2007 version

I would say before 2007 version !
​