# Countif Formula between Values from a Data Table

1. ## Countif Formula between Values from a Data Table

Hi,

In short I am trying to count values coming out from a data table, so that I can break the results down into approximate percentages.

Basically in H6 to H10005 I have predicted margin of victory.

Those margins of victory don't vary between +40 and -40 (e.g. an extreme result for one side or the other);

In cells C11 to C91 I have listed the margins -40 through to +40 (with C11 being -40).

To break the margins of victory down (e.g. for -40) I am looking to count the number of times a score between -40.5 and -39.5 occurs.

To do this in D11 I have gone for the following formula - but I have obviously made an error somewhere (as when copied through I am not getting anything counted from the 10000 results):

=COUNTIF(\$H\$6:\$H\$10005,"(>=(C11-0.5))"-COUNTIF(\$H\$6:\$H\$10005,"(<=(C11+0.5))"))

I'm struggling to see where I went wrong as the range is good and I thought the criteria was okay (e.g. if C11 is greater than or equal to -40.5 minus if C11 is less than or equal to -39.5). I'm sure I've got something basic the wrong way round so if anyone has any advice I'd be grateful.  Register To Reply

2. ## Re: Countif Formula between Values from a Data Table

Try: =COUNTIF(\$H\$6:\$H\$10005,">="&(C11-0.5))-COUNTIF(\$H\$6:\$H\$10005,"<="&(C11+0.5))

Dom  Register To Reply

3. ## Re: Countif Formula between Values from a Data Table

Thanks - at least I now know the convention for putting something like this together (I didn't figure the "&"). Now I need to just check what was wrong with the formula, as I've obviously made a mistake somewhere as all of the cells have come out as being 9998. I've expanded the range of margins of victory to -50 to +50 and I still have the same result so I've obviously not thought this through fully yet.  Register To Reply

4. ## Re: Countif Formula between Values from a Data Table

Try to change "<=" to ">" in 2nd COUNTIF  Register To Reply

5. ## Re: Countif Formula between Values from a Data Table

Thanks. I am going to go away and just work this through. It's one or the other - the data table is being slow to work through at the moment so I'm going to let it do it's thing and will post something back tomorrow.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 