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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Care to show one or 2 of your variations? It should work quite easily.
 

AlanAnderson

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

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
my poor attempt

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

Ranges do not match but the formula does
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

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:

AlanAnderson

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

Well-known Member
Joined
Mar 22, 2010
Messages
2,396

ADVERTISEMENT

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

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

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
ans = Application.WorksheetFunction.SumIf(Cells(15, 4).Resize(FinalRow, 4), "<>EXCLUDE", Cells(15, 3).Resize(FinalRow, 3))
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Wrong parentheses:

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

Forum statistics

Threads
1,141,018
Messages
5,703,756
Members
421,313
Latest member
Mooncake1

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
Top