Count only once if either Column A or Colum B has a value

[TODDLL]

I am trying to do a count if either column A or column B in the same row has a "Y" entered. As long as either column in that row has a "Y" entered, I would like the count to equal 1, otherwise a 0. Have been trying to figure out how to use the "or" statement but have not been successful.

Name	MCS Score B.AVG	PCS score B.AVG	COUNT
JOE	Y		1
SAM	Y	Y	1
MIKE			0
JOHN	Y		1
JIM			0
PETE			0

 

[jasonb75]

Probably 101 ways to do that, first 2 that come to mind

edit:- for clarity, the second formula assumes that the cells will either contain "Y" or be empty, any other content in the left column would cause a "Y" in the right column to be missed.

Book1(AutoRecovered)2 (version 1).xlsb
	A	B	C	D	E
1	Name	MCS Score B.AVG	PCS score B.AVG	COUNT
2	JOE	Y		1	1
3	SAM	Y	Y	1	1
4	MIKE			0	0
5	JOHN	Y		1	1
6	JIM			0	0
7	PETE			0	0
Sheet5

Cell Formulas
Range	Formula
D2:D7	D2	=--OR(B2="y",C2="y")
E2:E7	E2	=--(LEFT(B2&C2)="y")

 

[sandy666]

Name	MCS Score B.AVG	PCS score B.AVG	Count
JOE	Y		1
SAM	Y	Y	1
MIKE			0
JOHN	Y		1
JIM			0
PETE			0

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "Count", each if [MCS Score B.AVG] = "Y" or [PCS score B.AVG] = "Y" then 1 else 0)
in
    IF

 

[TODDLL]

Obviously I have not had any formal Excel training. This is the first time I have seen the 2 minus signs used. I had to do a search to understand their function. Thank you so much for your quick response. I am always impressed with and appreciative of the help on this site. AMAZING!

 

[jasonb75]

Glad to help and thanks for the feedback

The double minus method is quite common once you start getting into slightly more advanced formulas, mostly with sumproduct, but it can be used anywhere that you want to convert TRUE / FALSE result to 1 or 0.

For what you asked, a better known method would be

=IF(OR(B2="Y",C2="Y"),1,0)

which would have the added advantage of being able to show anything where a Y is found such as "Ok" and "Check" instead of 1 and 0, where as the -- version can only show 1 or 0.

 

[TODDLL]

Glad to help and thanks for the feedback

The double minus method is quite common once you start getting into slightly more advanced formulas, mostly with sumproduct, but it can be used anywhere that you want to convert TRUE / FALSE result to 1 or 0.

For what you asked, a better known method would be

=IF(OR(B2="Y",C2="Y"),1,0)

which would have the added advantage of being able to show anything where a Y is found such as "Ok" and "Check" instead of 1 and 0, where as the -- version can only show 1 or 0.

I was close but was trying to do it with a COUNTIF statement. Before I saw your first response, I did come up with something that worked. I USED a nested formula =IF(B2="Y",1,IF(C2="Y",1)). I still like your latest formula best and will use that instead. I suppose it is like you stated earlier. There a101 different ways to accomplish it. Thanks again for your support.