how to count one value or another across multiple columns?

[Guest]

I have thre columns: One holds names (record keys), another could hold "X",
the last can have a "Y". I want to know how many records have either an "X"
or a "Y" value?

For example:

Column A Column B Column C
Matt X
Bill Y
John X Y
Fred X Y
Tom

How many records (i.e. column A) have either an X or Y?

 

[Biff]

Hi!

What result do you expect based on your example?

6 or 4 ?

Biff

 

[Biff]

Doh!

It would help if I read the entire post!

Try this:

=COUNTA(A2:A6)-SUMPRODUCT(--(ISBLANK(B2:B6)),--(ISBLANK(C2:C6)))

Will work as long as there are no empty rows within the range.

Biff

 

[Franz]

I have thre columns: One holds names (record keys), another could
hold "X", the last can have a "Y". I want to know how many records
have either an "X" or a "Y" value?

For example:

Column A Column B Column C
Matt X
Bill Y
John X Y
Fred X Y
Tom

How many records (i.e. column A) have either an X or Y?

Supposing your range is A1:B5, you can use this formula:

=SUMPRODUCT((B1:B5="X")*(C1:C5="Y")) to see how many records have eihter X
*and* Y;

or

=SUMPRODUCT((B1:B5="X")+(C1:C5="Y")) to see how many records have either X
*or* Y.

--
Hoping to be helpful...

Regards

Franz

 

[Guest]

Thanks for the help!!!

I don't think this works though?...

Using the =SUMPRODUCT((B1:B5="X")*(C1:C5="Y")) formula you have for finding
which records have X *AND* Y, I get "1"... using my example data, it should
be "2"

Using the =SUMPRODUCT((B1:B5="X")+(C1:C5="Y")) formual you have for finding
which records have X *OR* Y, I get "5"... is should be "4"

Any ideas?

 

[Franz]

Thanks for the help!!!

I don't think this works though?...

Using the =SUMPRODUCT((B1:B5="X")*(C1:C5="Y")) formula you have for
finding which records have X *AND* Y, I get "1"... using my example
data, it should be "2"

Using the =SUMPRODUCT((B1:B5="X")+(C1:C5="Y")) formual you have for
finding which records have X *OR* Y, I get "5"... is should be "4"

IMHO with your data should be "6"...

Any ideas?

Maybe you have also spaces or blanks in the cell with X or Y; in this case
you can try these:

=SUMPRODUCT((RIGHT(B1:B5,1)="X")+(RIGHT(C1:C5)="Y"))

=SUMPRODUCT((RIGHT(B1:B5,1)="X")*(RIGHT(C1:C5,1)="Y"))



--
Hoping to be helpful...

Regards

Franz

 

[Guest]

Hmmm.. well I did have one space in there... but removing it didn't appear to
help.

I know get "6" for the OR equation. The AND equation result stayed the same
("2")

The answer I'm looking for in the OR equation is "4"... because four records
(i.e. rows or specifically the items in column A) contain either an X or a Y.

 

[Franz]

Hmmm.. well I did have one space in there... but removing it didn't
appear to help.

I think you should also have a leading blank before each X or Y (I think so
because I found it in your sample data...).

I know get "6" for the OR equation. The AND equation result stayed
the same ("2")

The answer I'm looking for in the OR equation is "4"... because four
records (i.e. rows or specifically the items in column A) contain
either an X or a Y.

Try this one, array entered (i.e. ctrl+shift+enter, instead of enter):

=SUMPRODUCT(IF((B1:B5="X")+(C1:C5="Y"),1,0))

--
Hoping to be helpful...

Regards

Franz

 

[Guest]

I think that's done it!! THANKS A BUNCH!!!

I think you should also have a leading blank before each X or Y (I think so
because I found it in your sample data...).



Try this one, array entered (i.e. ctrl+shift+enter, instead of enter):

=SUMPRODUCT(IF((B1:B5="X")+(C1:C5="Y"),1,0))

--
Hoping to be helpful...

Regards

Franz

 

[Franz]

I think that's done it!! THANKS A BUNCH!!!

Your welcome. Thanks for the feedback
--
Glad to be helpful...

Regards

Franz

 

[Aladin Akyurek]

=SUMPRODUCT(--(($B$2:$B$6="X")+($C$2:$C$6="Y")>0))