sumifs excluding certain items

Hi,

I cant get to a simple solution for this question I have???
I have a list of customers
I have a list of products and qtys sold
I have a list of dates(period)
I have a year

what I want is to sum the products sold to all customers EXCEPT a list
then report by period and year.

using sumifs does the trick except I would need to specificy each customer to exclude and reduce them from the total amount.gotta be a simpler smarter way????

test sheet.xlsx

test sheet.xlsx

hi ive attached a list of date and results table ( no formula)

as the actual data involves more than letters but names ive changed it around a bit

cheers

Hi - can i link a sumifs formula to a list - and then not equal to the list in some way??? eg a range

Hi there - I see that as a away of generating the solution but my data is live so wil chnage as wil the date periods going forward- I need to be able to chnage the table titles - and then refer to the data giving the result -

My original test sheet showed the result - but it didnt show that the data would change over time.

the headers eg P1 abnd year will change ( thats age for you!)

so the sumifs proposition is easy- until I want to exclude certain references eg dave, as well as colin-Im guessing this could be additional sumifs criteria - but would rather have reference to a table that would be excluded for the sum

solution above <> works for one - not what about more than one exclusion- or even better from a table that may change over time??

test sheet with answer!.xlsxHi - I amended the data to be a little more detailed as attached.

so I have few names that I would like to build into a list (so I can add and remove over time) that sums data from a data list which an abridged version is on there.

then I can create a report showing sales by period and year- by updating the titles it updates the details

stu

could I for instance exclude the references I dont want to sumif s eg sumifs ( data qty, <> derek&<>colin&<>Ian ) for example

Hi,

is this what you are looking for ? Try this formula into A17:
=SUM(B2:B15)-SUMPRODUCT(SUMIFS(B2:B15;C2:C15;A19:A21))

wow so we can use a list as a set of criteria

so that gives me the sum of the list without the slected names-I guess I just have ti rewrite the formual to include the period sections

it works!!!!!!! well done all

The formual sets works great- however I wa shoping to add it as a nmaed range but that doesnt work using define names-any ideas???