# Thread: Conditional Array (2000 SR2)

1. ## Conditional Array (2000 SR2)

Hi, I'm looking for an array formula that will count people whose hourly rate is about to change.

1_________5.20__________A
2_________5.80__________A
3_________5.30__________C

Count the number of posts whose grade is "A" and whose current hourly rate is between 5.00 and 5.49

So far I have {=COUNT(IF(AND(C2:C4="A",AND(B2:B4>=5.00,B2:B4<=5. 49)),A2:A4))}

But I'm getting some strange answers.

Thanks,
Jim MacLeod
Shetland Isles

2. ## Re: Conditional Array (2000 SR2)

Are you needing the answer in a cell to use in another calculation? If not...it would be just as effective to count the results of a filter.

3. ## Re: Conditional Array (2000 SR2)

Yes, I need to show the effect of a pay model on numbers of employees. What I'll end up with is:

_____________________Currently___Currently__Curren tly
A___________4.8__________3________7________0
B___________5.1__________4________4________0
C___________5.3__________3________2________2
D___________5.5__________5________4________3

4. ## Re: Conditional Array (2000 SR2)

Try

=SUMPRODUCT((B2:B4>=5)*(B2:B4<=5.49)*(C2:C4="A"))

as a normal formula, or

=SUM((B2:B4>=5)*(B2:B4<=5.49)*(C2:C4="A"))

as an array formula.

5. ## Re: Conditional Array (2000 SR2)

Try this: =COUNT(IF(AND(C2:C4="A"),AND(B2:B4>=5,B2:B4<=5.49) ,A2:A4))

Confirm entry by pressing CNTRL + SHIFT. (Do not type the curly braces in!)

6. ## Re: Conditional Array (2000 SR2)

Yes: =SUM((B2:B4>=5)*(B2:B4<=5.49)*(C2:C4="A")) works well.

PS: Ignore my reply! The more I tweeked it the more I messed it up!!

7. ## Re: Conditional Array (2000 SR2)

<P ID="edit" class=small>(Edited by sdckapr on 26-Sep-05 11:40. Forgot to include last column of table)</P><table border=1><td></td><td align=center>G</td><td align=center>H</td><td align=center>I</td><td align=center>J</td><td align=center>K</td><td align=center>L</td><tr><td align=center valign=bottom>1</td><td align=right valign=bottom>

8. ## Re: Conditional Array (2000 SR2)

Excellent, thanks for all your help

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•