COUNTIF Problem

endl3ss · Aug 17th 2016

Been mulling over this for a while and i still cant figure it out. Im trying to figure out how many "age groups" there are in each of a set of companies. I want to be able to "reset the count" when i hit another company in column a (not displayed). How could i do that?
[TABLE="class: cms_table, width: 427"]

[tr]

[/tr]

[tr]

[td]

Age

[/td]

[td]

Average Board Age

[/td]

[td]

First digit

[/td]

[td][/td]

[td]

Count

[/td]

[/tr]

[tr]

[TD="align: right"]68[/TD]
[TD="align: right"]54.58333333[/TD]

[td]

6

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]59[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]60[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]55[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]41[/TD]

[td][/td]

[td]

4

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]50[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]47[/TD]

[td][/td]

[td]

4

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]68[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]32[/TD]

[td][/td]

[td]

3

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]35[/TD]

[td][/td]

[td]

3

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]72[/TD]

[td][/td]

[td]

7

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]68[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]70[/TD]
[TD="align: right"]63.92857143[/TD]

[td]

7

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]71[/TD]

[td][/td]

[td]

7

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]55[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]57[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]72[/TD]

[td][/td]

[td]

7

[/td]

[TD="align: right"]0

[/TD]

[/tr]

[/TABLE]

endl3ss · Aug 17th 2016

Re: COUNTIF Problem

To do the counting i am using =(COUNTIF($AL$2:$AL2,$AL2)=1)+0

endl3ss · Aug 18th 2016

Re: COUNTIF Problem

bump can anyone help!?

**AliGW** · Aug 18th 2016

Re: COUNTIF Problem

Please provide a dummy column A so that we can see the full layout.

**AliGW** · Aug 18th 2016

Re: COUNTIF Problem

Also, in what format do you want to see the output?

endl3ss · Aug 18th 2016

Re: COUNTIF Problem

Quote from AliGW;775758

Also, in what format do you want to see the output?

Basically at the end of the day i want something like this. In column (age group (diff)) i want there to be a count of different numbers that show up for every company, and then in column G ill sum the number of 1s per company. So i can get the total nubmer of age groups. What im getting now for age group (diff) is the total count of different numbers appearing for both apple and bannas. But i want it to reset the count after every company.

For column Age group (diff) im using the formula: =(COUNTIF($AN$2:$AN6,$AN6)=1)+0
For the first digi column im using =LEFT($AL3,1)

[TABLE="width: 549"]

[tr]

[td]

Company

[/td]

[td]

AGE

[/td]

[td]

Average Age

[/td]

[td]

Age 10s

[/td]

[td]

Age Groups (Diff)

[/td]

[td]

# of Age Groups

[/td]

[/tr]

[tr]

[td]

Apple

[/td]

[TD="align: right"]68[/TD]
[TD="align: right"]54.58333[/TD]

[td]

6

[/td]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]

[/tr]

[tr]

[td]

Apple

[/td]

[TD="align: right"]59[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[td]

Apple

[/td]

[TD="align: right"]60[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Apple

[/td]

[TD="align: right"]55[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Apple

[/td]

[TD="align: right"]41[/TD]

[td][/td]

[td]

4

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[td]

Apple

[/td]

[TD="align: right"]50[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Apple

[/td]

[TD="align: right"]47[/TD]

[td][/td]

[td]

4

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Apple

[/td]

[TD="align: right"]68[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Apple

[/td]

[TD="align: right"]32[/TD]

[td][/td]

[td]

3

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[td]

Apple

[/td]

[TD="align: right"]35[/TD]

[td][/td]

[td]

3

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Apple

[/td]

[TD="align: right"]72[/TD]

[td][/td]

[td]

7

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[td]

Apple

[/td]

[TD="align: right"]68[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Bannana

[/td]

[TD="align: right"]70[/TD]
[TD="align: right"]63.92857[/TD]

[td]

7

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Bannana

[/td]

[TD="align: right"]71[/TD]

[td][/td]

[td]

7

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Bannana

[/td]

[TD="align: right"]55[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Bannana

[/td]

[TD="align: right"]57[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Bannana

[/td]

[TD="align: right"]72[/TD]

[td][/td]

[td]

7

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Bannana

[/td]

[TD="align: right"]63[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Bannana

[/td]

[TD="align: right"]68[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Bannana

[/td]

[TD="align: right"]61[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Bannana

[/td]

[TD="align: right"]63[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Bannana

[/td]

[TD="align: right"]64[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Bannana

[/td]

[TD="align: right"]68[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Bannana

[/td]

[TD="align: right"]64[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Bannana

[/td]

[TD="align: right"]67[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td]

Bannana

[/td]

[TD="align: right"]52[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[/TABLE]

**AliGW** · Aug 18th 2016

Re: COUNTIF Problem

OK - so you need a COUNTIFS query:

=COUNTIFS(A:A,A1,B:B,1)

where A is the company column and B is your count column.

endl3ss · Aug 18th 2016

Re: COUNTIF Problem

Quote from AliGW;775769

OK - so you need a COUNTIFS query:

=COUNTIFS(A:A,A1,B:B,1)

where A is the company column and B is your count column.

It wont work, because it will count people who are in there (for example) 60s more then once in one company. I'm stuck on column Age Groups (diff).

endl3ss · Aug 18th 2016

Re: COUNTIF Problem

Quote from AliGW;775769

OK - so you need a COUNTIFS query:

=COUNTIFS(A:A,A1,B:B,1)

where A is the company column and B is your count column.

at the end of the day i want this. Notice how the Age Group diffs recounted the 7,5,6 when the company changed
[TABLE="width: 613"]

[tr]

[td][/td]

[td]

Company

[/td]

[td]

AGE

[/td]

[td]

Average Age

[/td]

[td]

Age 10s

[/td]

[td]

Age Groups (Diff)

[/td]

[td]

# of Age Groups

[/td]

[/tr]

[tr]

[td][/td]

[td]

Apple

[/td]

[TD="align: right"]68[/TD]
[TD="align: right"]54.58333[/TD]

[td]

6

[/td]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]

[/tr]

[tr]

[td][/td]

[td]

Apple

[/td]

[TD="align: right"]59[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Apple

[/td]

[TD="align: right"]60[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Apple

[/td]

[TD="align: right"]55[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Apple

[/td]

[TD="align: right"]41[/TD]

[td][/td]

[td]

4

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Apple

[/td]

[TD="align: right"]50[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Apple

[/td]

[TD="align: right"]47[/TD]

[td][/td]

[td]

4

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Apple

[/td]

[TD="align: right"]68[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Apple

[/td]

[TD="align: right"]32[/TD]

[td][/td]

[td]

3

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Apple

[/td]

[TD="align: right"]35[/TD]

[td][/td]

[td]

3

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Apple

[/td]

[TD="align: right"]72[/TD]

[td][/td]

[td]

7

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Apple

[/td]

[TD="align: right"]68[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Bannana

[/td]

[TD="align: right"]70[/TD]
[TD="align: right"]63.92857[/TD]

[td]

7

[/td]

[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[/tr]

[tr]

[td][/td]

[td]

Bannana

[/td]

[TD="align: right"]71[/TD]

[td][/td]

[td]

7

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Bannana

[/td]

[TD="align: right"]55[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Bannana

[/td]

[TD="align: right"]57[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Bannana

[/td]

[TD="align: right"]72[/TD]

[td][/td]

[td]

7

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Bannana

[/td]

[TD="align: right"]63[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]1[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Bannana

[/td]

[TD="align: right"]68[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Bannana

[/td]

[TD="align: right"]61[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Bannana

[/td]

[TD="align: right"]63[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Bannana

[/td]

[TD="align: right"]64[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Bannana

[/td]

[TD="align: right"]68[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Bannana

[/td]

[TD="align: right"]64[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Bannana

[/td]

[TD="align: right"]67[/TD]

[td][/td]

[td]

6

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Bannana

[/td]

[TD="align: right"]52[/TD]

[td][/td]

[td]

5

[/td]

[TD="align: right"]0[/TD]

[td][/td]

[/tr]

[/TABLE]

**AliGW** · Aug 18th 2016

Re: COUNTIF Problem

No! The range referred to as B in my formula is the column where you have done the COUNTIF. That has already filtered out the duplicates!

endl3ss · Aug 18th 2016

Re: COUNTIF Problem

Quote from AliGW;775773

No! The range referred to as B in my formula is the column where you have done the COUNTIF. That has already filtered out the duplicates!

AHH. This is complicated, we not talking about the same thing! I dont care about total number of age groups, thats easy. The Age Group (DIFF) column is where im having a problem!!! I understand i removed the duplicates. But i want to remove the duplicates and reset for EACH company. So if theres a 70 year old in Apple and bannana, i want it to count 1 in BOTH apple and banana. Right now its counting at its first appearance in apple, but then at the first appearance in banana, its counting 0, because it hasnt reset the counter.

**AliGW** · Aug 18th 2016

Re: COUNTIF Problem

Ah, I see! OK - give me a minute.

endl3ss · Aug 18th 2016

Re: COUNTIF Problem

Quote from AliGW;775776

Ah, I see! OK - give me a minute.

thanks! Any luck!?

**NBVC** · Aug 18th 2016

Re: COUNTIF Problem

Does this work... assuming your data above is in column A:G, then in E2 (your Age Groups (Diff) column) enter:
[COLOR="#0000FF"]
=--(COUNTIFS($A$2:$A2,$A2,$D$2:$D2,$D2)=1)[/COLOR]

copied down.

endl3ss · Aug 18th 2016

Re: COUNTIF Problem

Quote from NBVC;775791

Does this work... assuming your data above is in column A:G, then in E2 (your Age Groups (Diff) column) enter:

=--(COUNTIFS($A$2:$A2,$A2,$D$2:$D2,$D2)=1)

copied down.

nope doesnt

**NBVC** · Aug 18th 2016

Re: COUNTIF Problem

Is it the column with the 1's and 0's that you are trying to calculate?

If so, then the formula should work. Make sure the ranges are set to match your actual ranges...

See attached for sample.

**AliGW** · Aug 19th 2016

Re: COUNTIF Problem

I've spent quite some time on this, but I think it now does what you want:

=(COUNTIF(INDIRECT("$D$"&MATCH(A2,$A$2:$A$18,0)+1&":D"&ROW()),INDIRECT("$D"&ROW()))=1)+0

NBVC's formula in post #16 also works, which is this:

=--(COUNTIFS($A$2:$A2,$A2,$D$2:$D2,$D2)=1)

COUNTIF Problem

Participate now!

Share