COUNTIF and Dynamic Ranges Not Working

derrickjp7

New Member
Joined
Jan 30, 2020
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
Hey Everyone,

I'm trying to use a dynamic range and COUNTIF to count how many "na" are within it. I'm confused why when the first cell in the range C3 is empty, it does not count any "na." But when I put "na" in C3 it counts both. Any suggestions? Take a look at the images for some screen shots. B3 and G3 also contain formulas.
 

Attachments

  • Ex1.JPG
    Ex1.JPG
    228.6 KB · Views: 27
  • Ex2.JPG
    Ex2.JPG
    250.8 KB · Views: 27

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
When you have NA in D3, COUNTA formula counts it as 1 and your INDEX formula creates a range C3:C3. As C3 does not have any NA, your formula returns zero.

When you NA in both C3 and D3, COUNTA formula counts to 2 and INDEX formula returns a range C3:D3 and therefore it returns 2.

Kind regards

Saba
 
Upvote 0
Why not just use
Excel Formula:
=Sheet1!$C$3:$F$3
for your defined name.
 
Upvote 0
you could use the following array formula instead of COUNTA

=MAX(IF(C3:G3="NA",COLUMN(C3:G3)-2))


1617072918091.png


Kind regards

Saba
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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