CODEWITHSUNDEEP
excelfrequencycountif
obilondo
obilondo

Reputation: 23

Count unique values from columnA if adjacent columnB meets a criteria and is not equal to a second criteria

This is my first post and hopefully I can explain this issue clearly.

Assuming the following raw excel table:

ColumnA ColumnB ColumnC             
1       W       A               
1       E       A               
1       W       B               
1       E       C               
1       E       D               
1       E       E               
1       W       E               
1       E       F               


Count of unique E in ColumnB    5               
Count of unique W in ColumnB    3

Task:

1) In counting unique values in ColumnC, count unique values that have a value of E in ColumnB.

2) In counting unique values in ColumnC, count unique values that have a value of W in ColumnB, taking into consideration that if an item in ColumnC has both E and W in ColumnB, it should only be counted for assumption 1 above. Only items in ColumnC that have only W in ColumnB should be included in this count.

As shown in my results, the count for items that have E in ColumnB is 5 which is correct. However, the count for items that have W in ColumnB is returning 3, but it should only return 1 because {1, W, B} is the only row where ColumnC item B has only a W in ColumnB.

The excel formula I have used for both counts are below:

Count for E

=SUM(IF(FREQUENCY(IF($A$2:$A$9="1",IF($B$2:$B$9="E",MATCH($C$2:$C$9,$C$2:$C$9,0))),ROW($C$2:$C$9)-ROW($C$2)+1),1))

Count for W

=SUM(IF(FREQUENCY(IF($A$2:$A$9="1",IF($B$2:$B$9="W",MATCH($C$2:$C$9,$C$2:$C$9,0))),ROW($C$2:$C$9)-ROW($C$2)+1),1))

Note the formula needs to be entered with Ctrl+Shift+Enter

How can I count for W and exclude rows from ColumnC that have both E and W in ColumnB?

Thanks in advance.

Upvotes: 2

Views: 1850

Answers (1)

barry houdini
barry houdini

Reputation: 46371

Try using this formula:

=SUM(IF(FREQUENCY(IF($A$2:$A$9="1",IF($B$2:$B$9="W",IF(COUNTIFS($A$2:$A$9,1,$B$2:$B$9,"E",$C$2:$C$9,$C$2:$C$9)=0,MATCH($C$2:$C$9,$C$2:$C$9,0)))),ROW($C$2:$C$9)-ROW($C$2)+1),1))

also using CTRL+SHIFT+ENTER

The additional COUNTIFS function gives you the extra functionality you need because that checks if there are additional rows with "E" against the relevant column C values

...or an alternative approach would be to count all different values from column C (assuming 1 in column A), and just subtract your "Count for E", so if that "Count for E" formula is in B12 use this array formula

=SUM(IF(FREQUENCY(IF($A$2:$A$9="1",MATCH($C$2:$C$9,$C$2:$C$9,0)),ROW($C$2:$C$9)-ROW($C$2)+1),1))-B12

Upvotes: 1

Related Questions