Countif

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

Thank you MarvinP you're a lifesaver!

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?

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

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

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

=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

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

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.

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?

Originally Posted by 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?

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

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?