PDA

View Full Version : [SOLVED] Countif or Similar Help Required and advise on Named Ranges



LutonBarry
02-10-2017, 11:40 AM
Folks, I've inherited a spreadsheet for tracking call incidents and outstanding actions. I've not laid it out as I would like and I'm told I cannot change the layout radically.

Basically you will see two tabs Mar and Apr. The March tab has 48 such call tracking areas. The Apr one has two to test the formulas but will be expanded to 48 if it works.

I have two named Ranges 'APRCalls' for cells F3 and N3 and 'APROpen' for cells I3 ans Q3.
To the left of the sheet is a Total Calls, Closed and Open alongside which I have entered formulas that currently do not work.


The total Calls I want to calculate the cells with an entry in that cell normally this is a number like CS12345 or SV12345. I currently have entered in cell B5 this formula =COUNTIF(AprCalls,"*") but that returns a #VALUE! I guess because the cells are not contiguous.

The other issue is counting the open calls. In 'AprOpen' cells I have entered =COUNTIF(K17:K19,"*no*") This works OK as long as there is not more than 1 'No' entered in K17:K19. What I want is a formula that looks at K17:K19 and return 0 if everything has a 'Yes' but 1 if there are 1 or more entries of 'No'.

Finally I will need to replicate this on the Mar Sheet with 48 call tracking areas then create separate tabs for the remaining calendar months. Is there an easy way the edit copied Named Ranges without having to select all the cells again on each of the monthly sheets.

Thanks in advance for any assistance.