CODEWITHSUNDEEP
excelexcel-formula
Andreas
Andreas

Reputation: 23968

COUNTIF(S) in two column and get the unique count

Is there any formula that can count in two columns and only return the unique count.
I have a table where two columns could have an "X", I want to count the "or" of this "X".
Meaning if it's in one or the other or both column(s) then count that, else don't.

Example:

 A       B        C
foo      X
bar               X
Doo      X        X
Boo

The expected result is 3 (foo, bar, Doo).
I can't do a =COUNTIF(B:B, "X") + COUNTIF(C:C, "X") since that will give me 4.
=COUNTIF(B:C , "X") returns 4.
SUMPRODUCT((B:B="X")*(C:C="X")*1) = 1, SUMPRODUCT((B:C="X")*1) = 4

Is there any formula that could count this case?

Sadly I don't have Office 365 at work

Upvotes: 0

Views: 1077

Answers (5)

Vipul Tawde
Vipul Tawde

Reputation: 336

This might be simpler. =COUNTIF(B:C,"X")-COUNTIFS(B:B,"X",C:C,"X")

It counts the total of all and then subtracts count of rows with both columns filled.

Upvotes: 0

JvdV
JvdV

Reputation: 75990

If one has Excel O365, you could try:

=COUNTA(UNIQUE(FILTER(A:A,(B:B="X")+(C:C="X"))))

If you have an older version of Excel, maybe try:

=SUM(--(FREQUENCY(IF((B1:B4="X")+(C1:C4="X")>0,MATCH(A1:A4,A1:A4,0)),ROW(A1:A4))>0))

Not, this last formula is an CSE-entered formula in Excel prior to O365. Also, if your data starts at a different row you'd need to adjust the formula accordingly. See this link that will explain how.

If you just want to count the rows that contain at least one "X", try:

=SUMPRODUCT(--(B:B&C:C={"X";"XX"}))

This would simply concatenate columns B&C and then check if the resulting value is either "X" or "XX". Depending if you do not even have headers it can be even simpler:

=SUMPRODUCT(--(B:B&C:C<>""))

If you want te minus the header: =SUMPRODUCT(--(B:B&C:C<>""))-1

Upvotes: 4

Vipul Tawde
Vipul Tawde

Reputation: 336

Use concat formula in column D such that D1=CONCAT(A1,B1) , D2=CONCAT(A2,B2) and so on.

D1=CONCAT(A1,B1)

Then use counta function on column D to count all cells that are not blank, simple as that , i hope.

=COUNTA(D:D)

Does that help you ?

OR Alternatively Try,

= COUNTIFS(B:B, "X",C:C, "") + COUNTIFS(B:B, "",C:C, "X") + COUNTIFS(B:B, "X",C:C, "X")

Upvotes: 3

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

See if below implementation of SUMPRODUCT is useful for you.

=SUMPRODUCT(--ISNUMBER(SEARCH("X",B2:B5&C2:C5,1)))

Or it needs to be tight and avoid issues like wild card matches etc. then you can also try:

=SUMPRODUCT(--(((B2:B5="X")+(C2:C5="X"))>0))

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 37125

If you have Excel365 then can use FILTER() function with COUNTA().

=COUNTA(FILTER(A1:A4,(B1:B4="x")+(C1:C4="x")))

enter image description here

Upvotes: 2

Related Questions