Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel IF and COUNT functions
I need to write an IF statement that look in the first column for a set value
and then counts the number of repetitions in column two. Column two contains dates which contain duplicates. The statement needs to count dupicate dates as single entries. |
#2
|
|||
|
|||
Try:
=SUM(N(FREQUENCY(IF(A1:A100=E1,MATCH (B1:B100,B1:B100,0)),MATCH(B1:B100,B1:B100,0))0)) Array-entered, meaning press ctrl + shift + enter, whe A1:A100 = 1st column B1:B100 = 2nd column (dates) E1 = value to look for in 1st column HTH Jason Atlanta, GA -----Original Message----- I need to write an IF statement that look in the first column for a set value and then counts the number of repetitions in column two. Column two contains dates which contain duplicates. The statement needs to count dupicate dates as single entries. . |
#3
|
|||
|
|||
=COUNT(1/FREQUENCY(IF(($B$1:$B$100<"")*($A$1:$A$100=E1),MA TCH($B$1:$B$100,$B$1:$B$100,0)),ROW(INDEX($B$1:$B$ 100,0,0))-ROW($B$1)+1))
which must be confirmed with control+shift+enter instead of just with enter. Robin Faulkner wrote: I need to write an IF statement that look in the first column for a set value and then counts the number of repetitions in column two. Column two contains dates which contain duplicates. The statement needs to count dupicate dates as single entries. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I count distinct names in an excel list? | Excel Discussion (Misc queries) | |||
Formula to make Excel count business days/skip weekends? | Excel Worksheet Functions | |||
How do I get a count when I filter an excel worksheet? | Excel Worksheet Functions | |||
Excel: How to return count for each cell within date range criter. | Excel Worksheet Functions | |||
How can I use count function in excel where I have several criter. | Excel Worksheet Functions |