Stop counting blank cells or Formulas as one

[Killer17]

Hey All

Need some help in fixing this formula to count correctly.

=COUNT(OFFSET(F61:J94,-INT(COUNTIF(F27:J60,">0")/3),0,MAX(ROWS(F61:J94)-INT(COUNTIF(F27:J60,">0")/3),INT(COUNTIF(F27:J60,">0")/3))))

Cell F27:F60 will have numbers only
Cell G27:G60 will have text only
Cell H27:H60 Will have numbers only
Cell I27:I60 Will have Text only
Cell J27:J60 will have numbers only

I know it's counting blank cells with formulas in Cell G27:G60 & I27:I60 as numbers, when it shouldn't count anything.

In Cell F27:F60 & Cell H27:H60 & Cell J27:60 are numbers any numbers in these's cells should be conuted as 1 in each cell.

Right not this formula is counting a number of 166 when it should only be counting as 102

Thanks

 

[T. Valko]

Hey All

Need some help in fixing this formula to count correctly.

=COUNT(OFFSET(F61:J94,-INT(COUNTIF(F27:J60,">0")/3),0,MAX(ROWS(F61:J94)-INT(COUNTIF(F27:J60,">0")/3),INT(COUNTIF(F27:J60,">0")/3))))

Cell F27:F60 will have numbers only
Cell G27:G60 will have text only
Cell H27:H60 Will have numbers only
Cell I27:I60 Will have Text only
Cell J27:J60 will have numbers only

I know it's counting blank cells with formulas in Cell G27:G60 & I27:I60 as numbers, when it shouldn't count anything.

In Cell F27:F60 & Cell H27:H60 & Cell J27:60 are numbers any numbers in these's cells should be conuted as 1 in each cell.

Right not this formula is counting a number of 166 when it should only be counting as 102

Thanks

Can you explain in words what you're trying to do?

 

[Killer17]

What I trying to achieve the amount of times a number is added into a cell that I have describe.

But empty cells are counting numbers when it's actually nothing in the cell other than a formula.

 

[T. Valko]

What I trying to achieve the amount of times a number is added into a cell that I have describe.

But empty cells are counting numbers when it's actually nothing in the cell other than a formula.

Well, that's not the kind of explanation I was hoping for!

Based on that explanation then a simple =COUNT(range) formula would work.

Why can't you use:

=COUNT(F27:F60,H27:H60,J27:J60)

Why are you using all that OFFSET stuff?

 

[MattMax]

I think I'm in the same situation..

I use a formula that reads a cell containing a jumble of text and pulls out a text value... Let's say Column A is the jumble text, and Column B contains the formula to parse A. If the formula detects a string it likes the cell in Column B will return a value... for example: "Network", if it does not find a value the cell is "" or blank.

Here is a countifs formula I use to detect instances of "Network" ::

=COUNTIFS(Master_sheet!V3:V4260,">" & H5,Master_sheet!V3:V4260,"<"&H6,Master_sheet!G3:G4260,"=Network")

This formula counts the number of records if fall between certain values (v column) and the parsed text string formula (G column) returns the value of "Network". Besides "Network" that cell could also be "Site" and "Channel" I would like to use a formula to encompass all possibilities. I tried to count the number cells that are not blank. The formula ::

=COUNTIFS(Master_sheet!V3:V4260,">" & H5,Master_sheet!V3:V4260,"<"&H6,Master_sheet!G3:G4260,"<>" & "")

It does not return the correct result. I have a feeling it is because it is reading through a range of formulas and "" is not considered a blank. In fact the isblank function is false for those blank cells

So to summarize is there a way I can count the number of cells in a column populated by formulas that have no txt value?

 

[T. Valko]

I think I'm in the same situation..

I use a formula that reads a cell containing a jumble of text and pulls out a text value... Let's say Column A is the jumble text, and Column B contains the formula to parse A. If the formula detects a string it likes the cell in Column B will return a value... for example: "Network", if it does not find a value the cell is "" or blank.

Here is a countifs formula I use to detect instances of "Network" ::

=COUNTIFS(Master_sheet!V3:V4260,">" & H5,Master_sheet!V3:V4260,"<"&H6,Master_sheet!G3:G4260,"=Network")

This formula counts the number of records if fall between certain values (v column) and the parsed text string formula (G column) returns the value of "Network". Besides "Network" that cell could also be "Site" and "Channel" I would like to use a formula to encompass all possibilities. I tried to count the number cells that are not blank. The formula ::

=COUNTIFS(Master_sheet!V3:V4260,">" & H5,Master_sheet!V3:V4260,"<"&H6,Master_sheet!G3:G4260,"<>" & "")

It does not return the correct result. I have a feeling it is because it is reading through a range of formulas and "" is not considered a blank. In fact the isblank function is false for those blank cells

So to summarize is there a way I can count the number of cells in a column populated by formulas that have no txt value?

Try this...

=COUNTIFS(Master_sheet!V3:V4260,">" & H5,Master_sheet!V3:V4260,"<"&H6,Master_sheet!G3:G4260,"?*")

The criteria: ?* will count all text entries but exclude formula blanks "".

 

[MattMax]

Wow! Thanks, worked like a charm.

 

[T. Valko]

Wow! Thanks, worked like a charm.

You're welcome. Thanks for the feedback!