Counting "tallies" in different columns without repeat

[bobaaholic]

Hi everyone, I'm not sure if this has been posted before but I couldn't find anything on it. I'm working on a project with characteristics being marked with two letters to indicated morning or evening for different columns and I'd like to count the cells without double-counting cells with related characteristics. I'd also like I've tried a few variations of COUNTIF but haven't really been able to figure it out.

For example, I'd like to count the number of people using Drug X or Drug Z. There are 3 people using Drug X and one person is using Drug X and Drug Z but I'd like to only count them as 3 people (instead of 4 non-empty cells for Drug X and Drug Z). Also I'd like to count the cells only with M. Hope that made sense!

Drug.xlsx
	A	B	C	D
1	Name	Drug X	Drug Y	Drug Z
2	Beth	M	E
3	Amy	M
4	Connor	M		E
Sheet2

 

[Scott T]

NVM did not read the whole post

 

[Michael M]

Is thtis on the right track

Book1
	A	B	C	D	E
1	Name	Drug X	Drug Y	Drug Z
2	Beth	M	E		1
3	Amy	M			1
4	Connor	M		E	1
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=IF(COUNTA(B2:D2)>0,1,"")

 

[Scott T]

Is this what yo want?

Book1
	A	B	C	D
1	Name	Drug X	Drug Y	Drug Z
2	Beth	M	E
3	Amy	M
4	Connor	M		e
5
6
7		3
Sheet1

Cell Formulas
Range	Formula
B7	B7	=SUMPRODUCT(($B$2:$B$4="m")+($D$2:$D$4="m"))-SUMPRODUCT(($B$2:$B$4="m")*($D$2:$D$4="m"))

 

[bobaaholic]

Is thtis on the right track

Book1
	A	B	C	D	E
1	Name	Drug X	Drug Y	Drug Z
2	Beth	M	E		1
3	Amy	M			1
4	Connor	M		E	1
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=IF(COUNTA(B2:D2)>0,1,"")

Sort of.

Book1
	A	B	C	D
1	Name	Drug X	Drug Y	Drug Z
2	Beth	M	E
3	Amy	M
4	Connor	M		e
5
6
7		3
Sheet1

Cell Formulas
Range	Formula
B7	B7	=SUMPRODUCT(($B$2:$B$4="m")+($D$2:$D$4="m"))-SUMPRODUCT(($B$2:$B$4="m")*($D$2:$D$4="m"))

Yes! This worked for me, thank you!