# Formula Required to count values over a range



## Arts (Oct 19, 2022)

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 (Oct 19, 2022)

Try this:

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


----------



## Arts (Oct 19, 2022)

Joe4 said:


> 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 (Oct 19, 2022)

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

You are welcome. Glad I was able to help!


----------



## Arts (Oct 19, 2022)

Joe4 said:


> 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 (Oct 19, 2022)

Arts said:


> 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 (Oct 19, 2022)

Joe4 said:


> 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 (Oct 19, 2022)

Arts said:


> 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 (Oct 19, 2022)

Joe4 said:


> 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 (Oct 19, 2022)

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.


----------



## Arts (Oct 19, 2022)

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


----------



## Arts (Jan 5, 2023)

Joe4 said:


> 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.



Hi Joe

Sorry to come back to this after a while, I was using another function and was speaking to someone on his forum and it got me thinking about something you mentioned earlier when you provided me with a formula using COUNTIFS

When using COUNTIFS would you know why the Criteria aspect needs to be in quotations? When doing this does excel not see this as TEXT?

So for instance in the two formulas below as you will with the COUNTIF the > is wrapped in "" (more a case of needs to be in quotations as it won't work otherwise)  but with the IF function the quotations aren't required.



 




Thanks


----------



## Joe4 (Jan 5, 2023)

The criteria needs to be in quotations to let Excel know that this literal text is the criteria you want.
By doing that, you could also use formula to build the criteria dynamically, which can come in handy.

See here for more details:





						COUNTIF function - Microsoft Support
					

How to use the COUNTIF function in Excel to count the number of cells that meet values you set.




					support.microsoft.com
				



and notice one of the examples shown to see how this works:

```
=COUNTIF(B2:B5,"<>"&B4)
```


----------



## Arts (Jan 5, 2023)

Joe4 said:


> The criteria needs to be in quotations to let Excel know that this literal text is the criteria you want.
> By doing that, you could also use formula to build the criteria dynamically, which can come in handy.
> 
> See here for more details:
> ...



Thank you Joe!


----------



## Joe4 (Jan 5, 2023)

You are welcome.


----------

