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?

Hi!

What result do you expect based on your example?

6 or 4 ?

Biff

"MeatLightning" wrote in message
...
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?

Doh!

It would help if I read the entire post!

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

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

"Biff" wrote in message
...
Hi!

What result do you expect based on your example?

6 or 4 ?

Biff

"MeatLightning" wrote in message
...
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?

"MeatLightning" ha scritto nel
messaggio

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

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

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" wrote:

"MeatLightning" ha scritto nel
messaggio

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

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

"MeatLightning" ha scritto nel
messaggio

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

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

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" wrote:

"MeatLightning" ha scritto nel
messaggio

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

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

"MeatLightning" ha scritto nel
messaggio

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

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

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

"Franz" wrote:

"MeatLightning" ha scritto nel
messaggio

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

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

"MeatLightning" ha scritto nel
messaggio

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

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

Regards

Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

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

MeatLightning wrote:
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?