Count The Rows in Remote sheet within two local numbers

Wesley De La Harpe

Hello,

we have delivery books with a certain number of delivery notes in it in a sequential range. in this example, there are 15 delivery notes in each book.

I Have 6 different delivery note book types.

Purchase Note Book
R.E.L Record Note
RO-RO Record Note
Scrap Container Service Record
Skip Record Note
Waste Control Receipt Book

but you will not get a duplicate range and book type together.

When we physically issue a delivery note number for a particular book, we update it in a sheet called "Manual DN Book Log" like this :

We want to track each book in a summary sheet, using the information from the above detailed sheet.

here is the summary sheet called "Manual DN Audit"

TheTYPE Column is a drop down which represents each column in the detailed sheet.

Count is a simple TO-FROM+1 and the FREE is a simple COUNT-USED

The items in used and free are correct but manually inserted for you.

I need the formula for each to count them automatically.

p.s. I can rename either this drop down or the remote column to be exactly the same if it will help the match.

My brain is not braining right now. 🤦‍♂️

Kind Regards,

WeZ

Soumitra Bhowmick

Assumtions

1. You have limited number of types
2. DN number are numeric

You can try something like below.

=COUNTIFS({DN}, ">=" + From@row, {DN}, "<=" + To@row, IF(Type@row = "Type 1", {T1}, IF(Type@row = "Type 2", {T2}, IF(Type@row = "Type 3", {T3}, IF(Type@row = "Type 4", {T4}, {T5})))), "Yes")

Steps

1. Create 6 different reference in Summary Sheet, for each column of different types from Manual DN Book Log
2. Create a separate reference in Summary sheet for DN number column from Manual DN Book Log
3. Use the formula as stated above and edit it accodingly

How it works

Smartsheet doesn't have formula to dynamically select column, so I used If to change the column depending upon the Type mentioned in Summary sheet.

Hope this helps.