Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values with Multiple Criteria
My data contains 2 columns of data
I want to count column A if column B has not already been counted. A B 1951 X911 2614 X911 2618 X911 2614 X911 1031 X911 2614 X911 2614 WAT 2614 WAT 2614 WAT 2614 WAT 2614 WAT In other words, I want to see how many different lines of X911. The answer I want is 3 because there are 3 different numbers associated with X911 HELP!!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values with Multiple Criteria
It's 4 not 3.
=SUM(--(FREQUENCY(IF(B1:B11="X911",MATCH(A1:A11,A1:A11,0) ),ROW(INDIRECT("1:"&ROWS(A1:A11))))0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Amber" wrote in message ... My data contains 2 columns of data I want to count column A if column B has not already been counted. A B 1951 X911 2614 X911 2618 X911 2614 X911 1031 X911 2614 X911 2614 WAT 2614 WAT 2614 WAT 2614 WAT 2614 WAT In other words, I want to see how many different lines of X911. The answer I want is 3 because there are 3 different numbers associated with X911 HELP!!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values with Multiple Criteria
=SUM(N(FREQUENCY(IF(rangeB="X911",MATCH(rangeA,ran geA,0)),MATCH(rangeA,rangeA,0))0))
ctrl+shift+enter, not just enter the answer returns 4 not 3 "Amber" wrote: My data contains 2 columns of data I want to count column A if column B has not already been counted. A B 1951 X911 2614 X911 2618 X911 2614 X911 1031 X911 2614 X911 2614 WAT 2614 WAT 2614 WAT 2614 WAT 2614 WAT In other words, I want to see how many different lines of X911. The answer I want is 3 because there are 3 different numbers associated with X911 HELP!!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values with Multiple Criteria
Thanks for the information but I am having a little trouble. Not a
surprise... :) I used this function but I am getting an error(#NUM!) and I am not sure why. I am working off of two sheets of data. My expression looks like this. =SUM(N(FREQUENCY(IF('Raw Data'!E:E=C2,MATCH('Raw Data'!C:C,'Raw Data'!C:C,0)),MATCH('Raw Data'!C:C,'Raw Data'!C:C,0))0)) Is there something on here that anyone can see? I am still new at this trying to learn. I did use the cntrl + shift + enter. "Teethless mama" wrote: =SUM(N(FREQUENCY(IF(rangeB="X911",MATCH(rangeA,ran geA,0)),MATCH(rangeA,rangeA,0))0)) ctrl+shift+enter, not just enter the answer returns 4 not 3 "Amber" wrote: My data contains 2 columns of data I want to count column A if column B has not already been counted. A B 1951 X911 2614 X911 2618 X911 2614 X911 1031 X911 2614 X911 2614 WAT 2614 WAT 2614 WAT 2614 WAT 2614 WAT In other words, I want to see how many different lines of X911. The answer I want is 3 because there are 3 different numbers associated with X911 HELP!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique values with criteria | Excel Worksheet Functions | |||
Counting Unique Items with Multiple Criteria | Excel Worksheet Functions | |||
Counting Unique Items with Multiple Criteria | Excel Worksheet Functions | |||
Counting Unique Items with Multiple Criteria | Excel Worksheet Functions | |||
Counting Unique Values Given Criteria | Excel Worksheet Functions |