Don't use data in row if a cell in a column of that row has 'X' value?

[Zack0611]

I don't know if this is possible at all, I've been trying to research it all day and have found nothing.

I am looking for a formula that will count the total number of cells in column B but not count the value of a cell if "Discontinued" is in the same row but in column C or D.

Thank you for your time, or please ask if there is anything I can make more clear!

 

[awoohaw]

so is your expected result 34 or 36?

 

[awoohaw]

Not sure what result you want, here are 3 formulas.

mr excel questions 17.xlsm
	A	B	C	D	E
1	Test Material
2	Subject Number
3		1	2	3	B,C,D Discontinue
4	1	0	0	0	34
5	2	0	0	0	only C,D Discontinue
6	3	0	0	0	34
7	4	0	0	0	Only B Disocontinue
8	5	0 Hr	0 Hr	0	35
9	6	0.5	0	0
10	7	0	0	0
11	8	0	0	0
12	9	0	0	0
13	10	0	0	0
14	11	0	0	0
15	12	0	0	0
16	13	0	0	0
17	14	1 Hr	0.5	0.5
18	15	0	0	0
19	16	0	0	0
20	17	0	0	0
21	18	0	0	0
22	19	DISCONTINUED	DISCONTINUED	DISCONTINUED
23	20	0	0	0
24	21	0.5	0	0
25	22	0	0	0
26	23	0	0	0
27	24	0	0	0
28	25	0	0	0
29	26	0	0	0
30	27	0 Hr	DISCONTINUED	DISCONTINUED
31	28	.5 d	0	0
32	29	0	0	0
33	30	DISCONTINUED	DISCONTINUED	DISCONTINUED
34	31	0	0	0
35	32	0	0	0
36	33	0.5	0.5	0
37	34	.5 d	0	0
38	35	0	0	0
39	36	0	0	0
40	37	0	0	0
Sheet5

Cell Formulas
Range	Formula
E4	E4	=SUMPRODUCT((--("DISCONTINUED"<>$B$4:$B$40))*(--("DISCONTINUED"<>$C$4:$C$40))*(--("DISCONTINUED"<>$D$4:$D$40)))
E6	E6	=SUMPRODUCT((--("DISCONTINUED"<>$C$4:$C$40))*(--("DISCONTINUED"<>$D$4:$D$40)))
E8	E8	=SUMPRODUCT((--("DISCONTINUED"<>$B$4:$B$40)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Zack0611]

34 is the expected result

 

[awoohaw]

okay, well, it seems one of the suggestions I gave above will work for you.