# sumif formula

#### dpaton05

##### Well-known Member
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
Re: Help with sumif formula

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

#### dpaton05

##### Well-known Member
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
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

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
Re: Help with sumif formula

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

#### Fluff

##### MrExcel MVP, Moderator

Re: Help with sumif formula

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

#### dpaton05

##### Well-known Member
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
Re: Help with sumif formula

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

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.

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.

### Which adblocker are you using?

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

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