Hello,
I am struggling a lot while using COUNTIF Formula withing Excel Table.
(Excel table means "Table" created by using CTRL+T).
Excel automatically changes COUNTIF Formula reference when I add new row to excel table. However excel gives me yellow triangle to autocorrect formula but I don't want to click and correct every time.
Please refer attachment.
Will be really grateful, if someone helps me on this.
i think it is because you are referring to an expanding range in a table - you don't need an expanding range as the table functionality already does that.
=COUNTIFS([Invoice],[@Invoice]) seems to work
Purfleet
Tables aren't really that good at that kind of formula. Simple way round it is to use something like:
=COUNTIF(INDEX([Invoice],2):[@Invoice],[@Invoice])
Thank you so much Mr. Velouria,
It really worked! I am very happy....
Hi Purfleet,
Thank you for your try..but that did not work as I wanted "1" on first occurrence, "2" on second occurrence, "3" on third occurrence and so on.
Your suggestion gave me total count against each value..
Thank you so much!
Hello,
Just minor change was required in the formula. I made "1" in place of "2" because if the table is having only one record then 1st record (occurrence) should give me result as "1" but it gave me "#REF!"
Below works fine:
=COUNTIF(INDEX([Invoice],1):[@Invoice],[@Invoice])
Thank you!
Jigar Panchal said
Hi Purfleet,Thank you for your try..but that did not work as I wanted "1" on first occurrence, "2" on second occurrence, "3" on third occurrence and so on.
Your suggestion gave me total count against each value..
Thank you so much!
Okay, fair enough. It wasnt clear from your question that you wanted to count 1, 2 and so on
