Countif

[Viessa]

I need a formula to count how many times the word Melissa appears in specific columns

 

[jtakw]

Hi,

If your columns are contiguous, then you can use COUNTIF with a range:

Excel 2010
	A	B	C	D	E	F
1	Melissa			George		3
2			Melissa
3		Paul
4	Paul			Melinda
5			John
6		Joe
7				Melissa
8		Jack
9
10
Sheet1

Cell Formulas
Range	Formula
F1	=COUNTIF(A1:D10,"Melissa")

 

[Viessa]

Only in column B and D

 

[mrhstn]

Using the same setup jtakw used try

=SUMPRODUCT((MOD(COLUMN($A$1:$D$10), 2)=0)*($A$1:$D$10="Melissa"))

There is likely some other, more elegant solutions.

 

[jtakw]

Only in column B and D

Hi,

If it's just the 2 Columns, you can simply sum the COUNTIF:

Excel 2010
	A	B	C	D	E	F
1	Melissa			George		4
2			Melissa
3		Paul
4	Paul	Melissa		Melinda
5			John
6		Melissa
7				Melissa
8		Jack
9				Melissa
10
Sheet2

Cell Formulas
Range	Formula
F1	=SUM(COUNTIF(B1:B10,"Melissa"),COUNTIF(D1:D10,"Melissa"))

 

[Viessa]

Not just to row 10 I have =countif(B:D,"MELISSA*") but if Melissa is mistakenly put in column c it is counting it and I don't want it to

=SUMPRODUCT((MOD(COLUMN($A$1:$D$10), 2)=0)*($A$1:$D$10="Melissa"))

There is likely some other, more elegant solutions.[/QUOTE]

 

[jtakw]

Not just to row 10 I have =countif(B:D,"MELISSA*") but if Melissa is mistakenly put in column c it is counting it and I don't want it to

=SUMPRODUCT((MOD(COLUMN($A$1:$D$10), 2)=0)*($A$1:$D$10="Melissa"))

There is likely some other, more elegant solutions.

[/QUOTE]

Actually, mrhstn's formula won't count Column C because it's counting only Even Columns, in this case, B and D.

However, you can use my simpler formula above in Post #5 , adjust the cell/row references to include your range of data.

 

[Aladin Akyurek]

Try...

=SUMPRODUCT((CHOOSE({1,2},B:B,D:D)="Melissa")+0)

There is nothing wrong with 2x COUNTIF(S) though...

 

[Viessa]

Thanks both ways worked

 

[jtakw]

You're welcome.