CountIf Formula with Greater Than & Less Than Not working

[zgadson]

Hello again! I've been working on a formula for my warehouse storage workbook that counts the number of available pallet spaces in an aisle between two user-generated inputs. My table shows "Aisle Information" based on the "User Input" columns. In my example below in the first line, the user has entered Aisle B1 with a starting point at slot 3, and ending at slot 56, so the user is asking to count the number of spaces between slots 3 and slot 40 in Aisle B1. The catch is that some of the slots between the user's start & end numbers already have pallets in place which is why I can't just count 3 to 40 and say there's 37 slots available.

My problem is that the "Slots Used" column is where my formula resides, but I cannot seem to get the Less Than part of the formula working. Every time I change it, it returns a result of 0.

=COUNTIFS(Table_Query[AISLE.NAME],[@ASSIGNMENT],Table_Query[SLOT],">"""&[START.ASSIGN],Table_Query[SLOT],">"""&[END.ASSIGN])

What am I doing wrong?

AISLE INFORMATION				USER INPUT			PALLET CALCULATION
TYPE	START	END	TTL.PALLET.SLOTS	ASSIGNMENT	START.ASSIGN	END.ASSIGN	SLOTS.USED	PALLETS REMAINING
RACK	3	56	279	B1	3	40	279	414
RACK	1	54	272	B2	1	54	272	414
RACK	1	52	261	B3	1	52	261	414
RACK	1	52	264	B4	1	52	264	414
RACK	1	52	267	C1	1	52	267	414
RACK	1	54	273	C2	1	54	273	414
RACK	1	54	272	D1	1	54	272	414
FLOOR	1	54	56	D2	1	54	56	414

 

[CA_Punit]

catch is that some of the slots between the user's start & end numbers already have pallets in place

How do we know this.?

Also what is the expected result in Slot used.

 

[zgadson]

How do we know this.?

Also what is the expected result in Slot used.

The user doesn't know which slots are empty or full, but once they put in the start/end slots they want, the "SLOTS USED" column should result in the number of available slots between their start/end input.

Real life example: I have a new account with 500 pallets coming into the building so I need to know where I can put it. I know aisle B1 has free space but I already have another account assigned to slots 41-56 in that aisle, so if I enter the starting and ending slots as 3 to 40 (excluding slots for the other account) I need to count the number of slots between 3 and 40, and counting the # of lines in my other table will return exactly that.

I basically just my formula to work when I switch the last ">" to "<". Seems so simple but it returns a 0 when I switch it. There's 29 available slots, which is what it should return.