Counting only cells with text/data, not formulas/numbers

[Henk]

All,

After three days, Its looks I'm going crazy.
I'm looking for a formula that does the opposite.
I have tried with almost all formulas but I can't figure it out.


All information is in the attached sample file.

I hope you can help me with this.
Thanks for contributing ideas.

Henk

[maclearyj]

Hello. hve had a quick look at your data, and some of the cells which look blank in fact have something in - the formulae in row 18 are being counted.

[Henk]

Hello maclearyj,

I only need to know if the cells are filled or not.

[Henk]

someone who can help me with this?

[AliGW]

I think you need to explain in more details what you are trying to do, what's not working and the results you expect. You'd have had a solution by now if these things were clear. Remember you KNOW exactly what's what - we don't, so you have to interpret your situation for us.

[Henk]

Ok, I try to explain.

I want to extend (change) the existing formula.

What I want to know is
if L9:R9 OR L12:R12 OR L15:R15 OR L18:R18 is filled with 1 or 2 or 3 ~ G set a flag (true else false)


If you choose week 2 then that applies to if S9:Y9 OR S12:Y12 OR S15:Y15 OR S18:Y18 is filled with 1 or 2 or 3 ~ G set a flag (true else false)

I can then continue to work with this flag.

Hopefully it is now clear.

[AliGW]

That doesn't get me any further forward, I'm afraid, Let's hope someone gets it. Just hang on in here!

[Limor_OP]

Hi,

From what I see all your results will be 21, no matter which weeknum you will enter in A1 , "Start-up" tab.

It's not true that week 3 is 28 since this : (Z9:AF9) and (Z12:AF12) and (Z15:AF15) will result in a count of 21.
So I don't understand what you are trying to do. You have 7 days a week * 3 which will always result in 21....

For convenience I added a formula in "Sheet 1" row 1 to identify the week numbers:

L1 = 1
M1 =IF(MOD(COUNT(L$1:$L1),7)=0,L1+1,L1) drag to the right.

Besides - notice that your numbers in I9:NI21 are formatted as text so you wont be able to sum them up, only count them.....

[Limor_OP]

Regarding your existing - formula
=COUNTA(OFFSET(Sheet1!H$9:H$20,,MATCH(A1,Sheet1!I$5:$NI$5,0),,7))

It is producing the "28" answer since it also counts the blank cells where there's a formula populated in them ,i.e., L18:R18
since COUNTA if for text content, and these cells are also considered as populated with a text - it is taken into account in your counting.

you can see it if you enter the ISBLANK function on one of these cells, =ISBLANK(L18) , and you will get a FALSE answer....

[Henk]

Belinda200,

Thank you for your effort, but that's not what I'm looking for.
The week numbers are already in row 5.

I try again,

If I choose in cell D9 the date of 04/01/2021 (dd / mm / yy) and in cell F9 choose Daily_Periodic, then L9: R9
filled with 1) L9 = 1, M9 = 1, N9 = 1 O9 = 1 P9-1 Q9 = -R9 = -
(If I choose Daily_Inspection than I get a 2 in the cells)
I think that is clear to everyone.

If I choose in D9 the date of 11/01/2021 (dd / mm / mm) and in cell F9 choose Daily_Periodic, then L9 is: R9
empty.

This also applies to row 12, row 15 and row 18.

I think this is also clear.

I just want to know if there is anything planned in week 1.
So check if one of these lines is filled with the number 1 to 0 and letters A to G.

**********
In other words,
If one of these lines is filled by the formula, put a true (or false or something) in another cell (for example on start-up sheet).
**********
Repeat this for week 2

With this true or false I can then continue to work with the sheet.

Is this a better description?

[Henk]

Belinda200,

At post #9,

Thats the reason that I want to change this formula,
See my post #10

But I'm a junior with excel ..............

[6StringJazzer]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Limor_OP]

Belinda200,
.........
Is this a better description?

Yes, much better. I can help you with the answer, but you need to change your thread title per administrative note in post #12....

[Limor_OP]

OK, it seems a bit condensed, but since the data is not well organized - the formulas will also become loaded.

Try this in A2 :

Please Login or Register  to view this content.

The formula lists one by one which characters you would like to include in your counting.
I included the following:

1. "G"
2. "-"
3. "1"
4. "2"
5. "3"

If you would like to add additional criteria just add another segment to the formula and change the part in red.
((OFFSET(Sheet1!H$9:H$20,,MATCH(A1,Sheet1!I$5:$NI$5,0),,7)="2")*(NOT(ISNUMBER(OFFSET(Sheet1!H$9:H$20,,MATCH(A1,Sheet1!I$5:$NI$5,0),,7)))))

Let me know if that works for you...

[Henk]

Hello Belinda200,

You save my whole week.
This is even better than I expected.

The whole week I was trying to implement such as sumproduct in the formula but not with success.
You did it in a couple of hours...

Thanks, now try to finish the workbook.

I put the thread on solved.

Henk

[Limor_OP]

Good to hear and glad to help.

Another way is using this formula that will count text which is not a number (you have "2" as a text and 2 as a number in your table - the first you want to count and other you don't), excluding the word "text" , and will not count the blank cells :

Please Login or Register  to view this content.

[6StringJazzer]

Good to hear and glad to help.

Belinda, thanks for the solution. The title is still terrible. I have never asked a member to do this but since you understood the question enough to solve it can you offer me a suggestion for a better title? Since the OP is a new member I'll just update it.

[AliGW]

I've changed the title - I didn't actually think it was that bad, apart from the bit about being sent crazy, but there we go! But you are right about the updated version, which was absolutely dreadful, completely generic and of no use whatsoever.

EDIT: I have used Belinda's suggestion below, which is much better than my attempt.

[Limor_OP]

Sure 6StringJazzer
He is trying to count cells that are formatted as text without blank cells that contain a formula , and without numbers.


So I would suggest: "Counting only cells with text/data, not formulas/numbers"

[FDibbins]

Just to ensure transparency in all we try to do here...another member reported 6String's moderation regarding the thread title (which I would probably also have done, had I seen it), and commented that another moderator had already posted on this thread - before the moderation was made.

Please keep in mind that we are all regular people here, and in many cases a moderation on changing a thread title is a subjective call, and different people sometimes read things differently.

Requesting a title change is not the end of the world, and certainly no reason to get upset or bent out of shape over it. If a moderation offends you that much, either just move on, or report it (either via the Report Post button, ir a PM to me)

Thanks for the understanding