Counting populated cells (range) in one column, that fall between 2 (diff) values in another column

[SnakeDoctor]

Good morning!

I have a worksheet for managing components in individual modules. So, each module title is a 'locked' row followed by rows for each component of that module.

Module Titles are in column A
Component Numbers are in column C

I need to count the number of components of each module. I have been trying to write a formula to count the cells of column C that are 'between' two different values in column A.

I have painfully figured out basic counting formulas for other data in this worksheet but have not had to create 'count if between', 'ranges' or 'IF this, and this, then Count this' type formulas.

Can anyone help me out?

[Alansidman]

I would do this with Power Query aka Get and Transform Data on the Data Tab. Once in PQ, use the GroupBy function to count the numbers. If you post a sample document, then I can provide the specific steps and code. I cannot manipulate data in a picture. Attach a sample file that is representative of your actual file making sure to anonymize any confidential data.

[SnakeDoctor]

Alansidman,

Thank you for the assist! Here is an example. I need to count the (non-blank) cells in the 'WORK CARD NUMBER' column (C), that follow the Module Title in the 'MODULE NUMBER ABD PREFIX' column (A).

So, how many Work Cards are in Module 1-GP, how many Work Cards are in Module 2-PD, etc...

[p45cal]

See table at cell B1136 of the attached.
Are they the correct values?
If so, we can explain more fully.

[SnakeDoctor]

That's it! Perfect! How did you pull it off?

[p45cal]

I used Power Query (Get & Transform Data) on the Data tab of the ribbon.
First I named the range you want to process A6:L1130 and called it myData (I did this to prevent Power Query converting the data into a proper Excel table).
Then with that range selected went into Power Query by clicking the From Table/Range button on the ribbon:

2024-07-17_140736.jpg

which brings up the Power Query window:

2024-07-17_143508.jpg

where I went through a few steps (on the right in the picture):

• Promoted the first row into headers,
• removed entirely blank rows,
• added a new column called IsHdrRow which looks to see if the MODULE NUMBER AND PREFIX column is blank or not, if it is then put TRUE else put FALSE,
• filled down the MODULE NUMBER AND PREFIX column,
• removed the header rows by filtering the IsHdrRow to leave only the FALSE rows,
• got rid of the blank rows in the WORK CARD NUMBER column so that merged cells are represented by a single row (this also got rid of Place Holder rows),
• then I grouped the rows according to column MODULE NUMBER AND PREFIX and got it to give me a count of rows in each group, which is the results table you see.

You need to make sure that when you change the data in your source data that (a) the named range myData covers it all and (b) you'll need to refresh the result table by right-clicking somewhere on it and choosing Refresh.

[SnakeDoctor]

Wow, that's a lot of beyond what I have done in the past!

Okay, I sort of follow you through the process. With the exception of where/how I name my selected data. If I click 'From Table/Range' of course it opens the 'Create Table' pop-up. I must be missing something...

[SnakeDoctor]

Never mind... Duh... R-Click, Define Name, yes?

[p45cal]

Select the range you want to process A6:L1130, then in name box here, type the name (if the name doesn't already exist you'll see the cell address A6, which you overwrite):

2024-07-17_170309.jpg

2024-07-17_170543.jpg

Changing its extent is a bit different if it already exists. You can go into Name Manager on the Formulas tab of the ribbon:

2024-07-17_170752.jpg
and either delete it in the dialogue box, then go back into the sheet and recreate it as before,
or
you can edit it directly in the dialogue box:

2024-07-17_171543.jpg

[SnakeDoctor]

Okay, what Custom Column Formula did you use to create the 'IsHdrRow' column?

[p45cal]

2024-07-17_173726.jpg


or you could use the user interface and go into Add column, then Conditional column and fill in for example as follows:




2024-07-17_174119.jpg


which results in the likes of:

= Table.AddColumn(#"Removed Blank Rows", "IsHdr", each if [MODULE NUMBER AND PREFIX] = null then false else true)

[SnakeDoctor]

I think it worked... IT DID! IT WORKED! p45cal, YOU ARE THE MAN! THANK YOU!!!!!

One more question, the result provided two columns, and I mistakenly told the 'Close and Load to..' function to populate both columns in my target. I only need the 'MyCount' column to populate my target.

I managed to remove the results from my target, but I can't find how to reset the load data to load only the 'MyCount' column to my target...

[SnakeDoctor]

Yea, the only option I have post-load is 'Close and Load'...

[p45cal]

I'm not at all clear; what is your 'target'?

[SnakeDoctor]

Apologies! I am 'loading' the results data into another tab in the same worksheet. There are a total of five tabs, one for each aircraft, in this worksheet.

Essentially, with your much appreciated help, I am building a comparison sheet to show results data of each tab side-by-side to determine what is missing/incorrect in each tab.

So, my target is another tab in the worksheet...