Formula to count the number of imported rows in a spreadsheet

[bearcub]

I need to create formula to count the number of rows on my spreadsheet that contain formulas (Sheet B).

I have section that contains "hard-coded" records. This are hard coded in gray. I then, after the last gray row, I have all the following rows linked to another sheet (Sheet A).

I need to create a check that data on sheet B matches the number of rows that are on Sheet A. Sheet B would have the hard-coded rows plus the linked rows

Here's an example:

Sheet A contains 300 rows (the last dirtied cell is 301 - 300 plus the header row)

Sheet B has 10 rows of hard coded rows plus all the linked rows to sheet A

That total used range on the sheet B is 308 (10 rows plus 298 rows of linked data).

As a result, I'm missing 2 names from the Sheet A so I have to go to sheet B and copy down 2 more rows so I've captured those missing members.

I'm trying to build in checks so that the user will easily know that they their missing a couple of people on their linked worksheet.

Thank you for your help in advance,

Michael

 

[sheetspread]

Do you have Excel 2013 or later? There's a formulatext function that will help do this. Or with F5(Goto)-special-formulas you can copy and paste them somewhere and count.

 

[bearcub]

We have 2016 - I didn't think about using formula text. I was thinking about using countif with ISformula as part of the critieria but countif doesn't handle arrays.

How would you count cells using the formulatext function?

Michael

 

[bearcub]

I think I found something that works:

Sumproduct(--(isformula(range)).

Then the thought occurred to me that what happens if I have a couple of blank rows that contain formulas. I don't want those include. Could I use something like len(range) <>0 inside the sumproduct function too:

Sumproduct(--(isformula(range)*Len(range)<>0))?

Or is this something out there that might cover both of these situations?

Thank you again for your help,

Michael

 

[sheetspread]

add <>"" as a condition

 

[bearcub]

Where would i add this condition inside the Sumproduct function?

 

[sheetspread]

I can only test with Excel 2010 (which doesn't have formulatext and isformula) but try:

=sumproduct(--(isformula(range)),--(range<>""))

len might work also

 

[bearcub]

I did try this and it seems to work. I copied down a couple of blank rows behind the last dirtied cell and it seemed to work:

SUMPRODUCT(--(ISFORMULA('[Alcosta Prelim.xlsb]All SCC Ready to Import'!$A:$A)),--(LEN('[Alcosta Prelim.xlsb]All SCC Ready to Import'!$A:$A)<>0))

thank you for your help,

Michael