Log In
Register
Home
Active Member
January 8, 2020
Offline
Hello,
I am struggling a lot while using COUNTIF Formula withing Excel Table.
(Excel table means "Table" created by using CTRL+T).
[I use COUNTIF very frequently almost each day. And I have ensured correct references in my formula.]
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.
See Answer
Trusted Members
December 20, 2019
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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])
Answers Post
Active Member
January 8, 2020
Offline
Thank you so much Mr. Velouria,
It really worked! I am very happy....
Active Member
January 8, 2020
Offline
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!
Active Member
January 8, 2020
Offline
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!
Trusted Members
December 20, 2019
Offline
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
1 Guest(s)
