[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1276
  • Last Modified:

VBA Excel sumif how to inclued a not equal criteria

I'm running some VBA code that passes to a sumif formula a string criteria I want that string criteria to be a not equal to stsement please see below, at the moment it adds everyting rather than excluding the search criteria

searchview= "<>CVN"

.SumIf(grnlable, SearchView, Range("tblCalc[Renewal_Premium]"))
0
Thrawn3000
Asked:
Thrawn3000
  • 3
  • 3
1 Solution
 
cyberkiwiCommented:
Is CVN the text "CVN"?
Try this

searchview= "<>""CVN"""
0
 
cyberkiwiCommented:
You can ignore the previous comment.
Now that I have had a chance to test it, this works fine for me (Excel 2010)

searchview = "<>CVN"
MsgBox WorksheetFunction.SumIf(Range("A1:A10"), searchview, Range("B1:B10"))

Is the text CVN exactly or does it have extra spaces after?
0
 
Thrawn3000Author Commented:
Cyberkiwi
there are no extra spaces it is exactly CVN
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
aikimarkCommented:
searchview = "<>'CVN'"
0
 
Thrawn3000Author Commented:
No luck with that
0
 
cyberkiwiCommented:
I think the key was to re-evaluate whether the text was in fact "CVN" exactly.
I was also of the mind like aikimark that quotes were required, but not so from VBA/worksheetfunction.
0
 
Thrawn3000Author Commented:
The syntax this solution perscribed is correct.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now