I have a dashboard tracking the number of pallets stock that we have stored at a warehouse. Each pallet has an individual location, but each location may appear multiple times if it's shared with other products, so I have been using the following formula to count the number of individual locations:
{=SUM(IF(FREQUENCY(IF('BATCH INPUT'!$H$2:$H$5000<>"",MATCH('BATCH INPUT'!$H$2:$H$5000,'BATCH INPUT'!$H$2:$H$5000,0)),ROW('BATCH INPUT'!$H$2:$H$5000)-ROW('BATCH INPUT'!$H$2)+1),1))}
I want to add another counter to track the number of individual locations that have a lock code (so something incorporating IF(ISBLANK), but I'm struggling to work out how to mesh these together - does anyone have an idea how it could be formulated? The ISBLANK check will be referring to a different column (J in this case) and there are 7/8 codes that would have to be uniquely named if necessary.
Thank you!
{=SUM(IF(FREQUENCY(IF('BATCH INPUT'!$H$2:$H$5000<>"",MATCH('BATCH INPUT'!$H$2:$H$5000,'BATCH INPUT'!$H$2:$H$5000,0)),ROW('BATCH INPUT'!$H$2:$H$5000)-ROW('BATCH INPUT'!$H$2)+1),1))}
I want to add another counter to track the number of individual locations that have a lock code (so something incorporating IF(ISBLANK), but I'm struggling to work out how to mesh these together - does anyone have an idea how it could be formulated? The ISBLANK check will be referring to a different column (J in this case) and there are 7/8 codes that would have to be uniquely named if necessary.
Thank you!
