Countif

[Matthewcooney]

Hi guys,

I'm using excel 2010 and have run into an issue on one of my spreadsheets. Currently I'm using a countif function to count all letter 'Y's as 1 and I need to add in so I can count another letter as 0.5. Does anyone know what formula I can add it?

The current formula I'm using is =COUNTIF(I16:I33, "*Y*")

Thanks

[AliGW]

COUNTIFS, perhaps?

[MarvinP]

Hi Matthew and welcome to the forum,

Perhaps

=COUNTIF(I16:I33, "*Y*") + COUNTIF(I16:I33, "*A*")/2

[Matthewcooney]

Thank you MarvinP you're a lifesaver!

[Matthewcooney]

I have one more question for you please

I'm using the formula =COUNTIF(H16:H278, "*") to count cells that contain text, but I need to count certain cells as 0.5 in the same range, is there a way I can do that?

[cbatrody]

Try the following, replace the letter "A" with text that you want to count as 0.5 in the same range:

=COUNTIF(H16:H278,"*")-COUNTIF(H16:H278,"*A*")+COUNTIF(H16:H278,"*A*")/2

[Matthewcooney]

It's going out as =COUNTIF(H16:H278, "*")-COUNTIF(H16:H278,"*A*"+COUNTIF(H16:H278,"*A*"/2))

I'm replacing "A" with a name does that matter? And it is still picking it up as 1 not 0.5

[samba_ravi]

attach a sample with desired result

[cbatrody]

Can you post an example file with an expected outcome in excel format please? Use the below URL for help on how to upload a file.

http://www.excelforum.com/members/da...ch-a-file.html

[Matthewcooney]

=COUNTIF(H16:H278,"*")-COUNTIF(H16:H278,"*stephen mcgowan*"+COUNTIF(H16:H278,"*stephen mcgowan*"/2))

I'm working with a column of different names and sometimes there will be the same name in 2 different cells. If there are 2 of same name in the column I need them to add up as 0.5 and single names to add up as 1

[Matthewcooney]

I have attached a sample.

In the candidate column If there is a double up of candidates, I need them to equal 1 not 2

[cbatrody]

Hi,

Try the following formula to obtain a count of unique candidates list.

=SUMPRODUCT((H16:H278<>"")/COUNTIF(H16:H278,H16:H278&""))

See the attached file, I have updated the formula in H9.

[MarvinP]

I don't understand your additional question. Could it be you need one of the other Count functions? See:

http://www.exceltip.com/tips/excel-2...ountblank.html

[Matthewcooney]

I don't think I explained it well haha.

If I have a column of candidate names and the formula is =COUNTIF(H16:H278,"*") counting text in each cell as 1, so 5 names equals 5. If the same name comes up twice, is there a way that I can write a formula so that a double up of a name equal 1 not 2?

[Immortal2014]

From A1 to A5 name Matthewcooney is in cell A2 and again in cell A5. The other three cells of range contains different names. Then count names and get the result, 4 different names in range A1:A5. Is that you want?

[cbatrody]

I don't think I explained it well haha.

If I have a column of candidate names and the formula is =COUNTIF(H16:H278,"*") counting text in each cell as 1, so 5 names equals 5. If the same name comes up twice, is there a way that I can write a formula so that a double up of a name equal 1 not 2?

Did you try the formula I suggested in Post #12?