Re: COUNTIF Problem
Quote from NBVC;775791Does 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
Posts by endl3ss
-
-
Re: COUNTIF Problem
Quote from AliGW;775776Ah, I see! OK - give me a minute.
thanks! Any luck!? -
Re: COUNTIF Problem
Quote from AliGW;775773No! 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. -
Re: COUNTIF Problem
Quote from AliGW;775769OK - 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
[tr]
[TABLE="width: 613"]
[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]
[TD="align: right"]68[/TD]
[TD="align: right"]54.58333[/TD]6
[/td]
[/tr]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[tr]
[td][/td]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]59[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]60[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]55[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]41[/TD]
[td]4
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]50[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]47[/TD]
[td]4
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]68[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]32[/TD]
[td]3
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]35[/TD]
[td]3
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]72[/TD]
[td]7
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]68[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Bannana
[/td]
[td]
[TD="align: right"]70[/TD]
[TD="align: right"]63.92857[/TD]7
[/td]
[/tr]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[tr]
[td][/td]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]71[/TD]
[td]7
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]55[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]57[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]72[/TD]
[td]7
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]63[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]68[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]61[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]63[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]64[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]68[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]64[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]67[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]52[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[/TABLE] -
Re: COUNTIF Problem
Quote from AliGW;775769OK - 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). -
Re: COUNTIF Problem
Quote from AliGW;775758Also, 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]
[TD="align: right"]68[/TD]
[TD="align: right"]54.58333[/TD]6
[/td]
[/tr]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[tr]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]59[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]60[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]55[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]41[/TD]
[td]4
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]50[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]47[/TD]
[td]4
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]68[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]32[/TD]
[td]3
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]35[/TD]
[td]3
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]72[/TD]
[td]7
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td]Apple
[/td]
[td][/td]
[TD="align: right"]68[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Bannana
[/td]
[td]
[TD="align: right"]70[/TD]
[TD="align: right"]63.92857[/TD]7
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]71[/TD]
[td]7
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]55[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]57[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]72[/TD]
[td]7
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]63[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]68[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]61[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]63[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]64[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]68[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]64[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]67[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]Bannana
[/td]
[td][/td]
[TD="align: right"]52[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[/TABLE] -
Re: COUNTIF Problem
bump can anyone help!?
-
Re: COUNTIF Problem
To do the counting i am using =(COUNTIF($AL$2:$AL2,$AL2)=1)+0
-
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?
[tr]
[TABLE="class: cms_table, width: 427"]
[/tr]
[tr]
[td]Age
[/td]
[td]Average Board Age
[/td]
[td]First digit
[/td]
[td][/td]
[td]Count
[/td]
[/tr]
[tr]
[td]
[TD="align: right"]68[/TD]
[TD="align: right"]54.58333333[/TD]6
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]59[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]60[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]55[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]41[/TD]
[td]4
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]50[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]47[/TD]
[td]4
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]68[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]32[/TD]
[td]3
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]35[/TD]
[td]3
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]72[/TD]
[td]7
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]68[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]
[TD="align: right"]70[/TD]
[TD="align: right"]63.92857143[/TD]7
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]71[/TD]
[td]7
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]55[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]57[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]72[/TD]
[td]7
[/td]
[TD="align: right"]0[/TD]
[/tr]
[/TABLE] -
Re: SUMIF Using Dynamic Ranges based on text similarities
Quote from NBVC;775025Display MoreHi,
Assuming your data is in A2:B9, try this formula in C2:
=IF(A2=A1,"",AVERAGEIF($A$2:$A$9,A2,$B$2:$B$9))
copied down.
Similar problem again, btu cant use this argument[TABLE="width: 427"]
[tr]
[td]Age
[/td]
[td]Average Board Age
[/td]
[td]First digit
[/td]
[td][/td]
[td]Count
[/td]
[/tr]
[tr]
[td]
[TD="align: right"]68[/TD]
[TD="align: right"]54.58333333[/TD]6
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]59[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]60[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]55[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]41[/TD]
[td]4
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]50[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]47[/TD]
[td]4
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]68[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]32[/TD]
[td]3
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]35[/TD]
[td]3
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]72[/TD]
[td]7
[/td]
[td][/td]
[TD="align: right"]1[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]68[/TD]
[td]6
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td]
[TD="align: right"]70[/TD]
[TD="align: right"]63.92857143[/TD]7
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]71[/TD]
[td]7
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]55[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]57[/TD]
[td]5
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[tr]
[td][/td]
[TD="align: right"]72[/TD]
[td]7
[/td]
[td][/td]
[TD="align: right"]0[/TD]
[/tr]
[/TABLE]
So im looking how many "Age Groups" there are in a given data set. Again i have apples and oranges in the far right. Im using =LEFT($A2,1) to get the first digit, and then using =(COUNTIF($A$2:$A2,$A2)=1)+0 to count the number of different unique digits. problem is, that I cant figure out the same way i did before, that is figuring out dynamic ranges, so i get different countifs for each category (apple, oranges -
Re: SUMIF Using Dynamic Ranges based on text similarities
thank you so much! I enver thought to use this kind of reasoning!
-
Re: SUMIF Using Dynamic Ranges based on text similarities
and thanks to whoever helps! sorry just realized i didnt say anything. was awfully rude of me
-
Hello,
Im wanting to do something like this. Where one formula will give me back the average of the entirety of the cells labeled APPLE, and then ORANGE, and etc. Im guessing i need an array, but im not sure how to do this one. I've been doing A2<>A3 to distinguish when the text changes, but i cant figure out a way to get a range in AVG(). Can someone help me?
[TABLE="width: 192"]
[tr]
[/tr]
[TD="width: 64"][/TD]
[TD="width: 64"]Stock[/TD]
[TD="width: 64"]Average[/TD]
[tr]
[td]Apple
[/td]
[/tr]
[TD="align: right"]123[/TD]
[TD="align: right"]10678.5[/TD]
[tr]
[td]APPle
[/td]
[td][/td]
[TD="align: right"]1234[/TD]
[/tr]
[tr]
[td]APPLE
[/td]
[td][/td]
[TD="align: right"]123[/TD]
[/tr]
[tr]
[td]APPLE
[/td]
[td][/td]
[TD="align: right"]41234[/TD]
[/tr]
[tr]
[td]ORANGES
[/td]
[/tr]
[TD="align: right"]123[/TD]
[TD="align: right"]3123[/TD]
[tr]
[td]ORANGES
[/td]
[td][/td]
[TD="align: right"]4123[/TD]
[/tr]
[tr]
[td]ORANGES
[/td]
[td][/td]
[TD="align: right"]4123[/TD]
[/tr]
[tr]
[td]ORANGES
[/td]
[td][/td]
[TD="align: right"]4123[/TD]
[/tr]
[/TABLE]