0

I'm trying to count the number of times a certain due date is missed in a summary sheet on my workbook. On the parent sheet when something is overdue we label it as "LT". Something like this: enter image description here and we'd count it with:

=COUNTIF(CNs!AH:AH,"=LT")

However it started displaying zero no matter what, as you can see the true value is 8 so in trying to implement a solution, I wrote an array formula which is as follows:

{=SUM(IF(ISERROR(SEARCH(LT,CNs!AH:AH)),1,0))}

The issue with my "fix" is that instead of zero it returns an exuberant figure: enter image description here

Since I'm working with a text string I felt the array would be more reliable but at this point I just need the correct value to show up. I'd like to thank you all in advance

Improve this question

1 Answer 1

Reset to default
0

Your first formula should work. Regarding your fix, you should change to:

{=SUM(IF(ISERROR(SEARCH("LT",CNs!AH:AH)),0,1))}

The 0 and 1 must be swapped for the sum to be correct.

Improve this answer
8
  • I did try that but when swapping the 0 and 1 it also returns 0 in the sum
    – Irving Lopez
    Commented Dec 15, 2021 at 18:45
  • @IrvingLopez See edit. I forgot to put the quote around LT.
    – Reddy Lutonadio
    Commented Dec 15, 2021 at 18:48
  • Even after adding the quotes, it's still 0
    – Irving Lopez
    Commented Dec 15, 2021 at 18:53
  • @IrvingLopez What is the result if you only do the sum for some cells and not the whole column? For example: {=SUM(IF(ISERROR(SEARCH("LT",CNs!AH2:AH7)),0,1))}
    – Reddy Lutonadio
    Commented Dec 15, 2021 at 19:06
  • Technically speaking AH2:AH7 is not the range (this sheet has a ton of entries) but interestingly if I go one by one it does count it if it has the LT condition ex. {=SUM(IF(ISERROR(SEARCH("LT",CNs!AH16)),0,1))}
    – Irving Lopez
    Commented Dec 15, 2021 at 19:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .