# 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.

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

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.

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

Try to change "<=" to ">" in 2nd COUNTIF

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.

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