sumif formula

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,714
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)
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,714
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
1,714
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,350
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
1,714

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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,547
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
1,714
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
46,547
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,397
Messages
5,528,495
Members
409,820
Latest member
gabrielrms

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top