sumif formula

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,362
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I need some help with a formula to calculate this, I think I need a sumif formula but I am not sure.


  • I have a excel table that calculates costs of services.
  • The table is called npss_quote.
  • There is a totals column called "Price ex. GST".
  • There is a drop down menu with 5 services and the header for this column is Service
  • There is one service that does not attract GST.
  • GST is a tax in Australia.
  • GST is a flat 10% of the cost of the item.
  • I can't use fixed references as there could be x number of rows in the table.

I am trying to make a formula in a cell below the table that calculates the total GST for all services in the invoice. This is the formula I have but it won't work. Could someone tell me what is wrong with it please?

Code:
=SUMIF(npss_quote[Service], " <>*Activities", npss_quote[Price 
ex. GST]*0.1)
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Re: Help with sumif formula

Just found the problem, needed to remove the space between " and <>
 
Upvote 0
Re: Help with sumif formula

No, that didn't fix it, still need help please?

I forgot to mention this in the first post,

  • The service that does not attract GST is *Services
 
Last edited:
Upvote 0
Re: Help with sumif formula

What does "it wont work" mean?

You're not getting the expected figure?

Try

=SUMIF(npss_quote[Service], "<>*Activities*", npss_quote[Price ex. GST]*0.1)

If this doesn't work isolate the problem by changing the condition to "=*Activities" so you get some data out.
If that figure works correctly then your "<>*Activities" should work ok.

Isolate the problem further by using a range instead of named ranges so you can investigate on a small amount of data.
 
Upvote 0
Re: Help with sumif formula

I keep getting an error saying, There's a problem with this formula.......

Even with what you suggested and the =*Activities also.
 
Last edited:
Upvote 0
Re: Help with sumif formula

Try
=SUMIF(npss_quote[Service], " <>*Activities", npss_quote[Price ex. GST])*0.1
 
Upvote 0
Re: Help with sumif formula

Try
=SUMIF(npss_quote[Service], " <>*Activities", npss_quote[Price ex. GST])*0.1

I still get an error with this. It highlights this part in the vba editor:

npss_quote[Price
 
Upvote 0
Re: Help with sumif formula

:confused: You were asking about a formula, yet you're now referring to the VBA editor.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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