Formulas not populating data in cell as required

Copper Contributor

Hi i have encountered an unknow error r where all other cells works perfectly by populating the data where this particular simple formula cannot  populate the figures it keeps coming as zero. there is no issue with the formula as other cells is working but for the request need it is refusing to give the data.

Can someone assist me please?

7 Replies

@Sheik_Chand 

That's too abstract question, perhaps you could give bit more details - which formula, on which range. For example, SUM(range) returns zero if range is formatted as text or have texts which looks like numbers.

@Sheik_Chand 

Populating data means showing either a number or a text. A zero is a number. And a zero is not an unknown error.

 

So your claim is unsubstantiated.

 

@Sheik_Chand 

 

what I meant is that  for example 

Sheik_Chand_0-1695657211881.png

as you can see the data is zero when there is no issues with the formulas.

=COUNTIF(CR66:CR85,"A1A")

=COUNTIF(CR66:CR85,"A1B") and so on 

 but for these 3 

=COUNTIF(CR66:CR85,"A1B")

=COUNTIF(CR66:CR85,"A3")

=COUNTIF(CR66:CR85,"A4")

 

Sheik_Chand_1-1695657504872.png

the cell is not giving the number i want form this range of data above it is still showing zero.

 

 

 

 

 

@Sheik_Chand 

In general formula works

image.png

But on second screenshot you have a lot of merged cells, perhaps here is an issue. Screenshot doesn't give details where the data is.

@SergeiBaklan 

the second sreenshot is where the data is all other one but the 3 are working correctly and these 3 it just unknown as if it was the merged cells it would have failed for all not just these 3

@Sheik_Chand 

Without the file it's hard to say why exactly the same formula works in one case and doesn't work in another

image.png

Another possible reason is circular reference, but that's just a guess.

Thank you all, I managed to fix this as I think sometimes excel just misbehaves. what I did was that I deleted the rows and then inserted new rows and added the formula and it worked for the A1B and A4 . But for the A3, I had to copy "A3" from my formula and paste it in my data range to calculate. weird but it worked.