Excel distinct count with conditions
A B C D E F G H I
115492 2009 10 42 5 90160624 15-Oct-09 WH 5
115492 2009 10 42 5 90160624 15-Oct-09 WH 5
115492 2009 10 42 5 90160624 15-Oct-09 WH 5
115492 2009 10 42 5 90160624 15-Oct-09 WH 5
115481 2009 10 44 6 90170587 30-Oct-09 WH 3
115481 2009 10 44 6 90170587 30-Oct-09 WH 3
115481 2009 10 44 6 90170587 30-Oct-09 WH 3
115481 2009 10 44 6 90170587 30-Oct-09 WH 3
115481 2009 10 44 6 90170587 30-Oct-09 WH 3
115481 2009 10 44 6 90170587 30-Oct-09 WH 3
115481 2009 10 44 6 90170587 30-Oct-09 WH 3
115481 2009 10 44 6 90170587 30-Oct-09 WH 3
115481 2009 10 44 6 90170587 30-Oct-09 WH 3
115520 2009 11 45 5 90174693 5-Nov-09 WH 3
115520 2009 11 45 5 90174693 5-Nov-09 WH 3
115520 2009 11 45 5 90174693 5-Nov-09 WH 3
115520 2009 11 45 5 90174693 5-Nov-09 WH 3
115502 2009 11 46 6 90179821 13-Nov-09 WH 3
115502 2009 11 46 6 90179821 13-Nov-09 WH 3
115502 2009 11 46 6 90179821 13-Nov-09 WH 3
115502 2009 11 46 6 90179821 13-Nov-09 WH 3
115502 开发者_运维百科 2009 11 46 6 90179821 13-Nov-09 WH 3
115502 2009 11 46 6 90179821 13-Nov-09 WH 3
115502 2009 11 46 6 90179821 13-Nov-09 WH 3
115502 2009 11 46 6 90179821 13-Nov-09 WH 3
115502 2009 11 46 6 90179821 13-Nov-09 WH 3
123057 2009 11 46 3 90182107 17-Nov-09 WH 3
123057 2009 11 46 3 90182107 17-Nov-09 WH 3
123057 2009 11 46 3 90182107 17-Nov-09 WH 3
123057 2009 11 46 3 90182107 17-Nov-09 WH 3
123056 2009 11 47 3 90186948 24-Nov-09 WH 3
123056 2009 11 47 3 90186948 24-Nov-09 WH 3
123056 2009 11 47 3 90186948 24-Nov-09 WH 3
123056 2009 11 47 3 90186948 24-Nov-09 WH 3
115506 2009 11 47 3 90186673 24-Nov-09 WH 5
115506 2009 11 47 3 90186673 24-Nov-09 WH 5
115506 2009 11 47 3 90186673 24-Nov-09 WH 5
115506 2009 11 47 3 90186673 24-Nov-09 WH 5
115506 2009 11 47 3 90186673 24-Nov-09 WH 5
115506 2009 11 47 3 90186673 24-Nov-09 WH 5
115506 2009 11 47 3 90186673 24-Nov-09 WH 5
115506 2009 11 47 3 90186673 24-Nov-09 WH 5
115496 2009 11 47 4 90187409 25-Nov-09 WH 3
115496 2009 11 47 4 90187409 25-Nov-09 WH 3
115496 2009 11 47 4 90187409 25-Nov-09 WH 3
115496 2009 11 47 4 90187409 25-Nov-09 WH 3
123058 2009 12 50 5 90198449 10-Dec-09 WH 3
123058 2009 12 50 5 90198449 10-Dec-09 WH 3
123058 2009 12 50 5 90198449 10-Dec-09 WH 3
123058 2009 12 50 5 90198449 10-Dec-09 WH 3
115522 2009 12 50 7 90200094 12-Dec-09 WH 7
115522 2009 12 50 7 90200094 12-Dec-09 WH 7
115522 2009 12 50 7 90200094 12-Dec-09 WH 7
115522 2009 12 50 7 90200094 12-Dec-09 WH 7
115522 2009 12 50 7 90200094 12-Dec-09 WH 7
123059 2009 12 51 6 90203897 18-Dec-09 WH 4
123059 2009 12 51 6 90203897 18-Dec-09 WH 4
123059 2009 12 51 6 90203897 18-Dec-09 WH 4
123059 2009 12 51 6 90203897 18-Dec-09 WH 4
115539 2009 12 51 7 90204074 19-Dec-09 WH 5
115539 2009 12 51 7 90204074 19-Dec-09 WH 5
115539 2009 12 51 7 90204074 19-Dec-09 WH 5
115539 2009 12 51 7 90204074 19-Dec-09 WH 5
115539 2009 12 51 7 90204074 19-Dec-09 WH 5
115539 2009 12 51 7 90204074 19-Dec-09 WH 5
115541 2009 12 51 7 90204389 19-Dec-09 WH 4
115541 2009 12 51 7 90204389 19-Dec-09 WH 4
115541 2009 12 51 7 90204389 19-Dec-09 WH 4
115541 2009 12 51 7 90204389 19-Dec-09 WH 4
115534 2009 12 52 2 90209074 28-Dec-09 WH 5
115534 2009 12 52 2 90209074 28-Dec-09 WH 5
115534 2009 12 52 2 90209074 28-Dec-09 WH 5
115534 2009 12 52 2 90209074 28-Dec-09 WH 5
115534 2009 12 52 2 90209074 28-Dec-09 WH 5
115534 2009 12 52 2 90209074 28-Dec-09 WH 5
considering a table above, How do I get distinct count of "A" column value where "I" has value "3"?
Heres an alternative that doesn't rely on unique items in column A being consecutive
=SUM(IF(FREQUENCY(A2:A76,A2:A76)>0,(I2:I76=3)*1,0))
Enter as an array formula
If I'm understanding correctly, you want a count of unique entries in column 'A' where column 'I' equals 3.
One (imperfect) solution would be:
=SUM((A1:A999<>A2:A1000)*(I2:I1000=3))+IF(A1=A2,0,IF(I1=3,1,0))
evaluated as an array formula.
Here is a "one shot solution":
- filter column
I>> http://www.contextures.com/xlautofilter01.html - select whole
Acolumn - Excel will display the
SUMand theCOUNTvalues on the bottom right
[EDIT] Btw, here are some tips for removing duplicates
[EDIT] Formula solution
- Add a formula in Column
J:=IF(COUNTIF(A3:$A$1000,A2)>1,0,1)(this will tell if there is another same value further in the range) - Drag and drop this formula till the end of your range
- Use a formula like Chris suggested ("kindof" because this is not an array formula):
=SUMPRODUCT((I2:I1000=3)*(A2:A1000))
加载中,请稍侯......
精彩评论