Forum Discussion
Formula that works only on original spreadsheet - Not on copy
Hello Excel's specialist,
I'm a good user of Excel but I'm stuck on this one.
I have an Excel sheet managing 2017 stuff with this formula >> =COUNTIF(INDIRECT(MATCH($E$9,B:B) & ":" & MATCH($E$9,B:B)),G14)
When it works well, this formula is supposed to check the value E9 in the column B:B and counts the number of value G14 in the line corresponding to E9.
The formula works properly on the 2017 spreadsheet but once I create a copy to manage 2018, it doesn't work.
I'm not the one who has created it and I do not understand the compilation of INDIRECTand MATCH functions.
The file is attached.
Can you please help to find what's wrong with it, maybe a hidden table....
Thank you in advance for your support.
Karim
- Detlef_LewinSilver Contributor
Karim,
I can't find any problems with the formula. It works in sheet 2017 and in sheet 2018.
- Glooby KCopper Contributor
Hi Lewin,
thanks for your attention on my question.
it's strange...
If you look at the formula in cell H9 the result should be "12" on the 6th of June.
There is no error message but it doesn't work as expected.
I did some tests and it appears that it's related to the dates column. I changed B32 by 09/01/2018 and B20 by 09/01/2018 >> In H9 I should find the number of times "TRM" is counted on the line 32.
It doesn't work. When I put back 2017, it works back.
There is something with the dates but I don't know what ?
Karim
- Detlef_LewinSilver Contributor
Karim,
why should H9 show 12 as a result for 6th of June when there are no entries in that row?
It seems you are talking about a totally different workbook. Perhaps you should upload that workbook.