Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count if
I need a formula that will count the unique text values of one column with a
specific value in another column. Here is an example: Date Text 200811 Cat 200901 Dog 200901 Mouse 200811 Frog 200811 Cat 200901 Bird If the date was 200811, then the unique values would be 2. If the date was 200901, then the unique values would be 3. Please let me know if you have any questions, thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count if
Assuming your dates are just numbers, we can use a helper column:
In C1 enter: 200811 this is the desired number for column A In C2 enter: =IF(A2<C$1,"",B2) and copy down In A1 thru C7 we see: Date Text 200811 200811 Cat Cat 200901 Dog 200901 Mouse 200811 Frog Frog 200811 Cat Cat 200901 Bird Note the unwanted dated are blanked out. Finally use: =SUMPRODUCT((C2:C1500<"")/COUNTIF(C2:C1500,C2:C1500&"")) which will display 2 -- Gary''s Student - gsnu200848 "Lindsey" wrote: I need a formula that will count the unique text values of one column with a specific value in another column. Here is an example: Date Text 200811 Cat 200901 Dog 200901 Mouse 200811 Frog 200811 Cat 200901 Bird If the date was 200811, then the unique values would be 2. If the date was 200901, then the unique values would be 3. Please let me know if you have any questions, thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count if
Sorry, but each dates will never have names that are the same, yet a certain
date will have repeat of certain names. For example, 200901 will have several repeats and so wil 200811. However, 200901 and 200811 will never have the same names. I am actually trying to pull this information into a table, where it will show me the number of unique names for each date. So, creating a seperate column everytime there is new data, the spreadsheet may become very large. To make this more complicated, there another column between the date a text column with 2 categories, that also needs to be a criteria along with the date. Date Number Text 200811 1 Cat 200901 2 Dog 200901 1 Mouse 200811 2 Frog 200811 1 Cat 200901 2 Bird "Gary''s Student" wrote: Assuming your dates are just numbers, we can use a helper column: In C1 enter: 200811 this is the desired number for column A In C2 enter: =IF(A2<C$1,"",B2) and copy down In A1 thru C7 we see: Date Text 200811 200811 Cat Cat 200901 Dog 200901 Mouse 200811 Frog Frog 200811 Cat Cat 200901 Bird Note the unwanted dated are blanked out. Finally use: =SUMPRODUCT((C2:C1500<"")/COUNTIF(C2:C1500,C2:C1500&"")) which will display 2 -- Gary''s Student - gsnu200848 "Lindsey" wrote: I need a formula that will count the unique text values of one column with a specific value in another column. Here is an example: Date Text 200811 Cat 200901 Dog 200901 Mouse 200811 Frog 200811 Cat 200901 Bird If the date was 200811, then the unique values would be 2. If the date was 200901, then the unique values would be 3. Please let me know if you have any questions, thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count if
This is the formula I used, it worked when I only had two if conditions and
then I added a third and now it is giving me an #VALUE =SUM(--(FREQUENCY(IF((AND($A$2:$A$50=E$1,$F$2:$F$50=$B41, $J$2:$J$50=$B$5)),MATCH($L$2:$L$50,$L$2:$L$50,0)), ROW(INDIRECT("1:"&ROWS($L$2:$L$50))))0)) "Lindsey" wrote: Sorry, but each dates will never have names that are the same, yet a certain date will have repeat of certain names. For example, 200901 will have several repeats and so wil 200811. However, 200901 and 200811 will never have the same names. I am actually trying to pull this information into a table, where it will show me the number of unique names for each date. So, creating a seperate column everytime there is new data, the spreadsheet may become very large. To make this more complicated, there another column between the date a text column with 2 categories, that also needs to be a criteria along with the date. Date Number Text 200811 1 Cat 200901 2 Dog 200901 1 Mouse 200811 2 Frog 200811 1 Cat 200901 2 Bird "Gary''s Student" wrote: Assuming your dates are just numbers, we can use a helper column: In C1 enter: 200811 this is the desired number for column A In C2 enter: =IF(A2<C$1,"",B2) and copy down In A1 thru C7 we see: Date Text 200811 200811 Cat Cat 200901 Dog 200901 Mouse 200811 Frog Frog 200811 Cat Cat 200901 Bird Note the unwanted dated are blanked out. Finally use: =SUMPRODUCT((C2:C1500<"")/COUNTIF(C2:C1500,C2:C1500&"")) which will display 2 -- Gary''s Student - gsnu200848 "Lindsey" wrote: I need a formula that will count the unique text values of one column with a specific value in another column. Here is an example: Date Text 200811 Cat 200901 Dog 200901 Mouse 200811 Frog 200811 Cat 200901 Bird If the date was 200811, then the unique values would be 2. If the date was 200901, then the unique values would be 3. Please let me know if you have any questions, thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |