CountIf Formula with Greater Than & Less Than Not working

zgadson

Board Regular
Joined
Jul 16, 2015
Messages
68
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

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

What am I doing wrong?

AISLE INFORMATIONUSER INPUTPALLET CALCULATION
TYPESTARTENDTTL.PALLET.SLOTSASSIGNMENTSTART.ASSIGNEND.ASSIGNSLOTS.USEDPALLETS REMAINING
RACK356279B1340279414
RACK154272B2154272414
RACK152261B3152261414
RACK152264B4152264414
RACK152267C1152267414
RACK154273C2154273414
RACK154272D1154272414
FLOOR15456D215456414
 
Last edited:

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 16, 2015
Messages
68
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,472
Messages
5,596,353
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top