So I am pulling out my hair here, trying to figure out why over the last two months, this formula has been working, and now it stopped. I have duplicated my previous work to the letter, I have double, triple, and quadruple checked my data sets. I have named and re-named my ranges to ensure they match previous work & the formula references, I have started from scratch working up, and nothing seems to make any sense as to why it isn't working now.

My formula is this (confirmed with CSE):

=if(countifs(ID_Range,$A3,Location_Range,$B3)=0,"N/A",countifs(ID_Range,$A3,Location_Range,$B3,Date1_Range,">="&Date2_Range)/countifs(ID_Range,$A3,Location_Range,$B3))

Again, I confirm with CSE (Control + Shift + Enter), then fill down.

It is spitting out all 0s (zeros). All data is found in the same workbook, just on different sheets, thus the use of ranges.

If I break the equation up into it's three parts: logical test, true, & false, I believe that my issue is stemming from the numerator of the false section.

Logical Test & true part:

=if(countifs(ID_Range,$A3,Location_Range,$B3)=0,"N/A"

This works. For any ID with Location that is not present, it is outputting "N/A", as expected.

False part:

I broke the False section up into its two parts: the numerator & denominator.

The denominator gives back as an output the number of items there are for each ID & Location match, as expected and needed.

The denominator is spitting back 0 for everything. I narrowed it down to: Date1_Range,">="&Date2_Range.

This has worked before, although I did run into something strange previously with it.

I used to have it has: Date2_Range,"<="&Date1_Range. It work for a month, then stopped working. So I switched them to what I have above, and it worked fine, until now.

Now either way I try it, I still get the same output of 0 (zero).

Logically, as far as I can tell, the formula makes sense. So that must mean that it is a data issue.

I check all dates in each date range and they are all valid dates. I check what would be the corresponding ID & Location for the data where Date1 & Date 2 are found, they are good too.

I just can't figure out why this isn't working now after 2 months of it working fine.

Any ideas, pointers, thoughts, suggestions, etc., is greatly appreciated!!

-Spydey

P.S. I don't have much hair left now .... my wife is going to kill me! She doesn't like me going bald ... hahahahahahaha

My formula is this (confirmed with CSE):

=if(countifs(ID_Range,$A3,Location_Range,$B3)=0,"N/A",countifs(ID_Range,$A3,Location_Range,$B3,Date1_Range,">="&Date2_Range)/countifs(ID_Range,$A3,Location_Range,$B3))

Again, I confirm with CSE (Control + Shift + Enter), then fill down.

It is spitting out all 0s (zeros). All data is found in the same workbook, just on different sheets, thus the use of ranges.

If I break the equation up into it's three parts: logical test, true, & false, I believe that my issue is stemming from the numerator of the false section.

Logical Test & true part:

=if(countifs(ID_Range,$A3,Location_Range,$B3)=0,"N/A"

This works. For any ID with Location that is not present, it is outputting "N/A", as expected.

False part:

I broke the False section up into its two parts: the numerator & denominator.

The denominator gives back as an output the number of items there are for each ID & Location match, as expected and needed.

The denominator is spitting back 0 for everything. I narrowed it down to: Date1_Range,">="&Date2_Range.

This has worked before, although I did run into something strange previously with it.

I used to have it has: Date2_Range,"<="&Date1_Range. It work for a month, then stopped working. So I switched them to what I have above, and it worked fine, until now.

Now either way I try it, I still get the same output of 0 (zero).

Logically, as far as I can tell, the formula makes sense. So that must mean that it is a data issue.

I check all dates in each date range and they are all valid dates. I check what would be the corresponding ID & Location for the data where Date1 & Date 2 are found, they are good too.

I just can't figure out why this isn't working now after 2 months of it working fine.

Any ideas, pointers, thoughts, suggestions, etc., is greatly appreciated!!

-Spydey

P.S. I don't have much hair left now .... my wife is going to kill me! She doesn't like me going bald ... hahahahahahaha

Last edited: