sumif formula

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Re: Help with sumif formula

Just found the problem, needed to remove the space between " and <>
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
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.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
Re: Help with sumif formula

Should Price ex. GST be in some form of quotes?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,297
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Re: Help with sumif formula

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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,297
Office Version
  1. 365
Platform
  1. Windows
Re: Help with sumif formula

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,299
Messages
5,836,484
Members
430,435
Latest member
Benforest1

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