Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check text in range of cells
I have names in a range of cells from D 4 - I 16. I would like to do two
things: 1) Check that each name occurs only one time in each column, D - I, and confirm with a True / False in another cell. 2) Check how many times a name appears in the entire range and print name with the number of times it appears to another cell. Is there a way to do these? Thanks, Tom |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check text in range of cells
To check for all unique:
=SUMPRODUCT(--COUNTIF(D4:I16,D4:I16))=COUNTA(D4:I16) To see how many times the name in A2 appears in range: =A2&" "&COUNTIF(D4:I16,A2) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "tommcbrny" wrote: I have names in a range of cells from D 4 - I 16. I would like to do two things: 1) Check that each name occurs only one time in each column, D - I, and confirm with a True / False in another cell. 2) Check how many times a name appears in the entire range and print name with the number of times it appears to another cell. Is there a way to do these? Thanks, Tom |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check text in range of cells
Hi!
To check all the unique name in a range =SUMPRODUCT(($D$4:$I$16<"")/COUNTIF($D$4:$I$16,$D$4:$I$16&"")) the use of "&" & "" used to avoid any blank cell or blank return by any formula in range. To check how many time a name in a cell A2 appear in range. =countif(d4:i16,a2) If a name in A2 appear only once in the range than return "True" other wise false =IF(COUNTIF($D$4:$I$15,A2)<2,"TRUE","FALSE") "Luke M" wrote: To check for all unique: =SUMPRODUCT(--COUNTIF(D4:I16,D4:I16))=COUNTA(D4:I16) To see how many times the name in A2 appears in range: =A2&" "&COUNTIF(D4:I16,A2) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "tommcbrny" wrote: I have names in a range of cells from D 4 - I 16. I would like to do two things: 1) Check that each name occurs only one time in each column, D - I, and confirm with a True / False in another cell. 2) Check how many times a name appears in the entire range and print name with the number of times it appears to another cell. Is there a way to do these? Thanks, Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I write formula to check a range of cells? | Excel Worksheet Functions | |||
How to check if a date falls within a range(2 cells: Start/End Dat | Excel Worksheet Functions | |||
check for FALSE values in a range of cells in VBA | Excel Discussion (Misc queries) | |||
Check if a cell contains text compared to a range of cells | Excel Discussion (Misc queries) | |||
How do I get Excel to display a range of cells with a check box? | Excel Discussion (Misc queries) |