Formula Required to count values over a range

[Arts]

Hi guys

I can't seem to think of what formula (combination) is required for the below.

I need to count the number of times that the below has a value in both cells, so for the below this should give me the value of 3 as this is the number of times Subject and Description have a value (entry).

I cant seem to think of what formula to use. I have no issue doing this over one row =COUNT(AND(ISTEXT(A2),ISTEXT(B2))) but am stuck when needing to do this over a range. I did try =COUNTA(AND(ISTEXT(A2:A6),ISTEXT(B2:B6))) but this doesn't seem to give me the outcome needed.

As always thank you for any assistance.

Arts

 

[Joe4]

Try this:

=COUNTIFS(A2:A6,"<>",B2:B6,"<>")

 

[Arts]

Try this:

=COUNTIFS(A2:A6,"<>",B2:B6,"<>")

It always makes me laugh when you get the response and it's looks so simple there was me messing around with ISTEXT, AND, COUNTA and you provide the above using one function!!

Thank you so much Joe4

Arts

 

[Joe4]

Yeah, I think we have all had that experience, at one time or another.

You are welcome. Glad I was able to help!

 

[Arts]

Yeah, I think we have all had that experience, at one time or another.

You are welcome. Glad I was able to help!

As per usual if I could ask a follow up for learning purposes. The <> means does not equal but in this case you have simply put it around quotations? Usually it's followed up with something ie what is it we do not want it to equal too i.e <> ""

 

[Joe4]

As per usual if I could ask a follow up for learning purposes. The <> means does not equal but in this case you have simply put it around quotations? Usually it's followed up with something ie what is it we do not want it to equal too <> ""

Sure, no problem.
When you write it like that, it is sort of a shortcut way of writing "not equal to nothing" (meaning, it is actually "equal to something").

 

[Arts]

Sure, no problem.
When you write it like that, it is sort of a shortcut way of writing "not equal to nothing" (meaning, it is actually "equal to something").

Ah I see I'm stuck in the dark ages I would normally write it as such within a formula =IF(A5<>"","Result","Entry needed")

 

[Joe4]

Ah I see I'm stuck in the dark ages I would normally write it as such within a formula =IF(A5<>"","Result","Entry needed")

The difference in that is in the COUNTIFS, the <> is already between double-quotes, whereas in your IF statement, it is not.
So to try to put something that already contains double-quotes, like <>"", inside of double-quotes gets a little messy.
Luckily, we don't need to in this case.

 

[Arts]

The difference in that is in the COUNTIFS, the <> is already between double-quotes, whereas in your IF statement, it is not.
So to try to put something that already contains double-quotes, like <>"", inside of double-quotes gets a little messy.
Luckily, we don't need to in this case.

Oh ok I get it now in that the criteria aspect of the COUNTIFS requires it to be in "" so the <> doesn't need the double quotes.

It does cross my mind in that who is it that taught guys like you (by that I mean individuals that seem to know it all!) all of this, did you just pick up a book and it stuck? Really is quite incredible,

Anyway I'm sure there are other people that need your help with actual excel queries rather than non excel queries I'm now starting to dive into.

Thank you once again for all of your help!!

Arts

 

[Joe4]

You are welcome.

I had picked up a few books (especially VBA early in my career), but a lot of what I picked up is from this site, just by reading posts that interest me and seeing how others handle it.