Counting Occurences

I was given the array formula below to count the occurences of "12" &
"11.5" in a column of numbers.

=SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),{12;11.5}))

The number of teams competing can vary, the above example assumes 12
teams. If there is a dead heat, the points are shared so they get 11.5
each.

I have a cell AN1 that now holds the number of teams competing, so I
would like to change the {12;11.5} above to {$AN$1;$AN$1-0.5}, but keep
getting an error. If I can do this, then instead of changing all my
formulas, all I need to do is change the value in AN1.

Hope that makes sense

Pete

Try using 2 cells?

Put in AN1: 12, in AN2: =AN1-0.5

Then use, array-entered (press CTRL+SHIFT+ENTER):
=SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),AN1:AN2))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Pete" <[email protected]> wrote in message
news:[email protected]...
> I was given the array formula below to count the occurences of "12" &
> "11.5" in a column of numbers.
>
> =SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),{12;11.5}))
>
> The number of teams competing can vary, the above example assumes 12
> teams. If there is a dead heat, the points are shared so they get 11.5
> each.
>
> I have a cell AN1 that now holds the number of teams competing, so I
> would like to change the {12;11.5} above to {$AN$1;$AN$1-0.5}, but keep
> getting an error. If I can do this, then instead of changing all my
> formulas, all I need to do is change the value in AN1.
>
> Hope that makes sense
>
> Pete
>

Hi Pete

One way:

=SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),$AN$1-{0,0.5}))

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Pete" <[email protected]> skrev i en meddelelse
news:[email protected]...
>I was given the array formula below to count the occurences of "12" &
> "11.5" in a column of numbers.
>
> =SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),{12;11.5}))
>
> The number of teams competing can vary, the above example assumes 12
> teams. If there is a dead heat, the points are shared so they get 11.5
> each.
>
> I have a cell AN1 that now holds the number of teams competing, so I
> would like to change the {12;11.5} above to {$AN$1;$AN$1-0.5}, but keep
> getting an error. If I can do this, then instead of changing all my
> formulas, all I need to do is change the value in AN1.
>
> Hope that makes sense
>
> Pete
>

Hi Leo,

Nice to see you around. It's much neater,
but perhaps there was a typo? Should it be:
=SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),$AN$1-{0;0.5}))
(with the semicolon instead of the comma in " {0;0.5} ")
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Leo Heuser" <[email protected]> wrote in message
news:[email protected]...
> Hi Pete
>
> One way:
>
> =SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),$AN$1-{0,0.5}))
>
> --
> Best Regards
> Leo Heuser
>
> Followup to newsgroup only please.

Hi Max

You are very kind :-)

Yes, you are right. It should have been a semicolon.

Thanks!

Regards
LeoH


"Max" <[email protected]> skrev i en meddelelse
news:[email protected]...
> Hi Leo,
>
> Nice to see you around. It's much neater,
> but perhaps there was a typo? Should it be:
> =SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),$AN$1-{0;0.5}))
> (with the semicolon instead of the comma in " {0;0.5} ")
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Leo Heuser" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Pete
>>
>> One way:
>>
>> =SUM(COUNTIF(OFFSET(G$1,{0,13,25,37},,12),$AN$1-{0,0.5}))
>>
>> --
>> Best Regards
>> Leo Heuser
>>
>> Followup to newsgroup only please.
>
>

It's good to see you around, Leo !
Cheers
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Thanks Leo works fine.

You're welcome, Pete, and thanks for the feedback :-)

LeoH


"Pete" <[email protected]> skrev i en meddelelse
news:[email protected]...
> Thanks Leo works fine.
>