SUMIF in VBA

AlanAnderson

Board Regular
Joined
Jun 7, 2010
Messages
134
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Care to show one or 2 of your variations? It should work quite easily.
 
Upvote 0
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
 
Upvote 0
my poor attempt

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

Ranges do not match but the formula does
 
Upvote 0
It should be more like:
Ans=Application.WorksheetFunction.SumIf(Range("C15:C21"),"<>EXCLUDE", Range("D15:D21"))

have adjusted the ranges to match in length
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
ans = Application.WorksheetFunction.SumIf(Cells(15, 4).Resize(FinalRow, 4), "<>EXCLUDE", Cells(15, 3).Resize(FinalRow, 3))
 
Upvote 0
Wrong parentheses:

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

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top