SUMIF in VBA

[AlanAnderson]

Hi,
I am trying to get subtotals on several columns - say D, E and F

The subtotals should EXCLUDE any rows where value in c21 to CFinalrow = "EXCLUDE"
I've tried several variations of Application.WorksheetFunction.Sumif with no success.

Is there a better way than using SumIf?

Regards,

Alan

 

[GlennUK]

Care to show one or 2 of your variations? It should work quite easily.

 

[AlanAnderson]

Hi Glen,

Ans=Application.WorksheetFunction.SumIf(("C15:C21"),"<> EXCLUDE", ("D15:D20"))

This gives Type mismatch.

In any event once I try substitute "Cells (15,3) and (FinalRow,3) it all falls apart.

Regards,

Alan

 

[Dryver14]

my poor attempt

=SUM(E14:E21)-SUMIF(G14:G21,"exclude",E14:E21)

Ranges do not match but the formula does

 

[GlennUK]

It should be more like:
Ans=Application.WorksheetFunction.SumIf(Range("C15:C21"),"<>EXCLUDE", Range("D15:D21"))

have adjusted the ranges to match in length

 

[AlanAnderson]

Hi Dryver,
Thanks I appreciate you help but really don't understand what you are doing. My column containing the "EXCLUDE" is"C" and is entirely Text items, the columns I wish to add is column D

Thanks,

Alan

 

[Dryver14]

Glenn has given you the answer i think

just to expplain what I was doing and I did say the ranges were wrong


Based on what is there it should have read

=SUM(D15:D21)-SUMIF(C15:C21,"Exclude",D15:D21)

I did it this way as I could not work out how to say <>"Exclude" but I see Glenn has included the "<> Exclude" all within the quotation marks

 

[AlanAnderson]

Hi,
Thanks both Glenn and Dryver - that now works. If however I want to use FinalRow rather than a hard coded number I am still battling. This is what I have done:
ans=Application.worksheetfunction.Sumif(Range(Cells(15,3)),(Cells(FinalRow,3)), "<>EXCLUDE",Range(Cells(15,4)), (Cells(FinalRow,4)).
This falls over on Sumif - wrong number of arguments.

Am I being dumb or what?

Thanks,

Alan

 

[Dryver14]

ans = Application.WorksheetFunction.SumIf(Cells(15, 4).Resize(FinalRow, 4), "<>EXCLUDE", Cells(15, 3).Resize(FinalRow, 3))

 

[RoryA]

Wrong parentheses:

ans=Application.worksheetfunction.Sumif(Range(Cells(15,3),Cells(FinalRow,3)), "<>EXCLUDE", Range(Cells(15,4), Cells(FinalRow,4))