Countif Headache

[VBA learner ITG]

Hi all,

This should be something easy to sort.

But I have gone formula crazy after staring at the screen for so long i cannot figure out why my formula is not outputting correctly,

I have done everything in terms of recreating a new formula, google search and now i need your expertise if possible.

As you can see from the link to the spreadsheet the Countif should be creating a sequence number for all grouped values but for some reason its restarting after it hits 27 in the number sequence.

Any ideas to why this would be?

File In question

 

[Fluff]

Which row is it failing on?
Also your formula can simply be written as

=COUNTIF($A$2:A2,A2)

 

[VBA learner ITG]

Which row is it failing on?
Also your formula can simply be written as

=COUNTIF($A$2:A2,A2)

Hi Fluff,

That was my original starting formula.

As I need to add some variables to the grouping value thats why i have written it in the way i have. If you look at cell A98 shows 27 and then on A98 restarts from 1 when it should be 28 in count-if value sequence because i declared that these values should be together: GROCERYCERTASSCOTLANDCERTAS LICENSEDCERTASSCOTLANDCERTAS FRESHFROZENCERTASSCOTLANDCERTAS

 

[Fluff]

Not sure if I understand, but is this what you are trying to do

=IF(COUNTIFS(scotland,A2),SUM(COUNTIFS(A$2:A2,scotland)),IF(COUNTIFS(wales,A2),SUM(COUNTIFS(A$2:A2,wales)),IF(COUNTIFS(england,A2),SUM(COUNTIFS(A$2:A2,england)),COUNTIFS(A$2:A2,A2))))

 

[VBA learner ITG]

Which row is it failing on?
Also your formula can simply be written as

=COUNTIF($A$2:A2,A2)

Hi Fluff,

So I declared that any instances of these groups should be Countif together:

Countif to include all instances of these values:
GROCERYCERTASSCOTLANDCERTAS
LICENSEDCERTASSCOTLANDCERTAS
FRESHFROZENCERTASSCOTLANDCERTAS

Countif to include all instances of these values:
GROCERYCERTASENGLANDCERTAS
LICENSEDCERTASENGLANDCERTAS
FRESHFROZENCERTASENGLANDCERTAS

Countif to include all instances of these values:
GROCERYCERTASWALESCERTAS
LICENSEDCERTASWALESCERTAS
FRESHFROZENCERTASWALESCERTAS

Any-other value in column A can be treated with the normal CountIF formula

 

[VBA learner ITG]

Not sure if I understand, but is this what you are trying to do

=IF(COUNTIFS(scotland,A2),SUM(COUNTIFS(A$2:A2,scotland)),IF(COUNTIFS(wales,A2),SUM(COUNTIFS(A$2:A2,wales)),IF(COUNTIFS(england,A2),SUM(COUNTIFS(A$2:A2,england)),COUNTIFS(A$2:A2,A2))))

Hi fluff,

Thanks you as always. It works as expected. I cant thank you enough for your time.

 

[Fluff]

You're welcome & thanks for the feedback.