not getting expected result

I cannot explain why the original formula returns zero and the modified formula returns #VALUE.

But your count will be incorrect, regardless.

When all other conditions are true and the corresponding F2:F10000 contains "on off", it will be counted twice because it matches both "*off" and "on off".

-----

Your original formula might return zero because there is no row where all conditions are true in column A, O and F.

Your formula counts one (or two by mistake) for each row in 2:10000 where column A equals A684, and column O equals "table", and column F equals "started" or "on off" or ends with "on" or "off".

Is that your intent?

But as BSB noted, you must SUM the COUNTIFS results in order to get the full count.

Without SUM, your formula returns only the count of cells in each row in 2:10000 where column A equals A684, column O equals "table", and column F equals "started".

Perhaps there is no such row.

-----

The modified formula might have returned #VALUE because you did not apply the suggestion correctly. Always show us each formula that you use.

Following BSB's suggestion, the modified formula should be:

Please Login or Register  to view this content.