sumif formula

[dpaton05]

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?

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

 

[dpaton05]

Re: Help with sumif formula

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

 

[dpaton05]

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

 

[Special-K99]

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]

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.

 

[Special-K99]

Re: Help with sumif formula

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

 

[Fluff]

Re: Help with sumif formula

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

 

[dpaton05]

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]

Re: Help with sumif formula

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