find how many times a value is in range B3:D12

**Ironchef** · 03-10-2008, 11:48 AM

What i want to do is:

To find a value in B3:D12
Count how many times that value is written
Give me the number

Anyone know what kind of formula i must use? VLOOKUP maybe?

**NBVC** · 03-10-2008, 11:50 AM

Try COUNTIF

e.g. =Countif(B3:D12,1) counts how many 1's in the range

See Excel help on this function

**Ironchef** · 03-10-2008, 12:08 PM

It worked at first, but not when I wanted to:

If the E column don't has a value, e.g. E3 = "", it will skip the row from the counting.

I tried this formula:
=IF(E3="";"";COUNTIF(B3:D3;J3))+IF(E4="";"";COUNTIF(B4:D4;J3))+IF(E5="";"";COUNTIF(B5:D5;J3))+IF(E6="";"";COUNTIF(B6:D6;J3))+IF(E7="";"";COUNTIF(B7:D7;J3))+IF(E8="";"";COUNTIF(B8:D8;J3))+IF(E9="";"";COUNTIF(B9:D9;J3))+IF(E10="";"";COUNTIF(B10:D10;J3))+IF(E11="";"";COUNTIF(B11:D11;J3))+IF(E12="";"";COUNTIF(B12:D12;J3))

It worked at first when the E column had a value on all rows, but when it didnt have a value, I get an error message: #value!

**NBVC** · 03-10-2008, 12:11 PM

So you tried =Countif(B3:D12,J3) ?

What kind of data is in B3:D12 and what is in J3?

**Ironchef** · 03-10-2008, 12:18 PM

I've uploaded the worksheet.

**NBVC** · 03-10-2008, 12:23 PM

If you are copying the formula down, then you will need to "freeze" the range...

Try: =COUNTIF($B$3:$D$12,J3) copied down.

**Ironchef** · 03-10-2008, 12:33 PM

Yeah, I know. But the problem occur if a match isn't played yet.

e.g. If Team E and Team B haven't played yet, the cells E12 and G12 will be blank. But when i "blank" it, K3 gives me the error #VALUE! Copying the formula isn't the problem.

What i want the formula to do, is that if a match haven't been played yet, it will not count it in the match's played column.

Do you understand the problem?