List of criterea to exclude

Hi all,

First time poster, been reading a lot but I've encountered a problem which I cannot solve by searching the internet.

I have a table with Data, the table is called Table1 and has the following Headers:

Driver; Date; Sector; Thread; Passengers moved; Capacity; Occupancy.

So I am filtering on:

- Date
- Driver
- Sector (exclusions)

So I am using the following formula:

Quote:

---

=SUMIFS(Table1[Pax moved],Table1[Driver],Numbers!B5,Table1[Date],">="&Numbers!$C$1,Table1[Date],"<="&Numbers!$C$2,Table1[Sector],"<>"&$H$2,Table1[Sector],"<>"&$H$3,Table1[Sector],"<>"&$H$4,Table1[Sector],"<>"&$H$5,Table1[Sector],"<>"&$H$6,Table1[Sector],"<>"&$H$7,Table1[Sector],"<>"&$H$8,Table1[Sector],"<>"&$H$9,Table1[Sector],"<>"&$H$10,Table1[Sector],"<>"&$H$11,Table1[Sector],"<>"&$H$12,Table1[Sector],"<>"&$H$13,Table1[Sector],"<>"&$H$14,Table1[Sector],"<>"&$H$15,Table1[Sector],"<>"&$H$16,Table1[Sector],"<>"&$H$17,Table1[Sector],"<>"&$H$18,Table1[Sector],"<>"&$H$19,Table1[Sector],"<>"&$H$20,Table1[Sector],"<>"&$H$21,Table1[Sector],"<>"&$H$22,Table1[Sector],"<>"&$H$23,Table1[Sector],"<>"&$H$24,Table1[Sector],"<>"&$H$25,Table1[Sector],"<>"&$H$26,Table1[Sector],"<>"&$H$27,Table1[Sector],"<>"&$H$28,Table1[Sector],"<>"&$H$29,Table1[Sector],"<>"&$H$30,Table1[Sector],"<>"&$H$31,Table1[Sector],"<>"&$H$32,Table1[Sector],"<>"&$H$33,Table1[Sector],"<>"&$H$34,Table1[Sector],"<>"&$H$35,Table1[Sector],"<>"&$H$36,Table1[Sector],"<>"&$H$37,Table1[Sector],"<>"&$H$38,Table1[Sector],"<>"&$H$39,Table1[Sector],"<>"&$H$40,Table1[Sector],"<>"&$H$41,Table1[Sector],"<>"&$H$42,Table1[Sector],"<>"&$H$43,Table1[Sector],"<>"&$H$44,Table1[Sector],"<>"&$H$45,Table1[Sector],"<>"&$H$46,Table1[Sector],"<>"&$H$47,Table1[Sector],"<>"&$H$48,Table1[Sector],"<>"&$H$49,Table1[Sector],"<>"&$H$50,Table1[Sector],"<>"&$H$51,Table1[Sector],"<>"&$H$52)

---

Which is a very bulky formula. And I figured that the last part (sectors) could be made a bit shorter, I have tried the AND function and tried to but it in an ARRAY, without success.

To clarify:
- The list of sectors to be excluded is in H1:H52
- Start date is in C2, End date is in C3

So the code is working but I'd like it to be less bulky if I ever need to amend the location of the list.

All help is appreciated,

Thanks! :)

Untested.

=SUMPRODUCT(--(ISNA(MATCH(Table1[Sector],$H$2:$H$52,0))),--(Table1[Driver]=Numbers!B5),--(Table1[Date]>=Numbers!$C$1),--(Table1[Date]<=Numbers!$C$2),Table1[Pax moved])

Thanks admin,

Works perfectly!

Cheers,

You are welcome ! :cheers: