Formula Required to count values over a range

Arts

Well-known Member
Joined
Sep 28, 2007
Messages
765
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.


1666183540471.png


As always thank you for any assistance.

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.

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.

1672935307843.png
1672935364848.png



Thanks
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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:
and notice one of the examples shown to see how this works:
Excel Formula:
=COUNTIF(B2:B5,"<>"&B4)
 
Upvote 0
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:
and notice one of the examples shown to see how this works:
Excel Formula:
=COUNTIF(B2:B5,"<>"&B4)

Thank you Joe!
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top