Opsintern
06-09-2015, 08:10 AM
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:
=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! :)
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:
=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! :)