COUNTIF formula

[DarrenF]

Hello,

I'm trying to use the following formula:

=COUNTIF(D2,"Vanilla")+COUNTIF(D2,"BlueBerry")+COUNTIF(D2,"Cherry")

I also would like to add if cell (D2) is blank, then use the data in Cell (C2). I'm not sure how to write that part of the formula.

Example Data Sheet:

[TABLE="width: 253"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD] C[/TD]
[TD] D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Cherry[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Vanilla[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Blueberry[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Strawberry[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Vanilla[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]Vanilla[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]Blueberry[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Why are you using COUNTIF if you are just ever looking at one single cell at a time?
Also, will both columns C and D ever both be populated at the same time?

If not, maybe this will give you what you want:

=IF(OR(C2&D2="Vanilla",C2&D2="Blueberry",C2&D2="Cherry"),1,0)

 

[DarrenF]

Thanks Joe for your help!

Yes, both columns can be populated at the same time.

 

[DarrenF]

I think I got it:

=IF(OR(AND(D2="",C2="Vanilla"),D2="Vanilla",D2="Blueberry",D2="Cherry"),1,0)

 

[Dave Patton]

Excel 2010
	C	D	E	F	G	H	I
1					Count by criteria		Blueberry
2		Cherry	1	1	1		Cherry
3		Vanilla	1	1	1		Vanilla
4		Blueberry	1	1	1
5		Strawberry	0	0	0
6	Banana		0	0	0
7	Cherry		1	1	1
8		Banana	0	0	0
9	Vanilla	Vanilla	1	1	1
10		Blueberry	1	1	1
11
5d

Cell Formulas
Range	Formula
E2	=IF(D2="",OR(C2={"Vanilla","BlueBerry","Cherry"}),OR(D2={"Vanilla","BlueBerry","Cherry"}))+0
F2	=IF(D2="",COUNTIF(rCriteria,C2),COUNTIF(rCriteria,D2))
G2	=COUNTIF(rCriteria,IF(D2="",C2,D2))

Named Ranges
Name	Refers To	Cells
rCriteria	='5d'!$I$1:$I$3