Array Formula

[sabbathnut]

Hello

I was wondering if anyone could help with this array formula

Column A is customer
Column B is the percentage

A B
Smiths .14
Smiths .25
Bobs -.14
Bobs 0
Smiths .96
Smiths -.65


I need to know by specific customer how many times within there products they are below 15% and greater than -15%

Maybe it’s Sunday and my brain isn’t working I cannot work it out

 

[T. Valko]

Hello

I was wondering if anyone could help with this array formula

Column A is customer
Column B is the percentage

A B
Smiths .14
Smiths .25
Bobs -.14
Bobs 0
Smiths .96
Smiths -.65


I need to know by specific customer how many times within there products they are below 15% and greater than -15%

Maybe it’s Sunday and my brain isn’t working I cannot work it out

Try one of these...

Use cells to hold the criteria:

• D2 = 0.15
• E2 = -0.15
• F2 = Smiths

This one will work in any version of Excel:

=SUMPRODUCT(--(A2:A7=F2),--(B2:B7>=E2),--(B2:B7<=D2))

This one will work in Excel versions 2007 and later:

=COUNTIFS(A2:A7,F2,B2:B7,">="&E2,B2:B7,"<="&D2)

 

[sabbathnut]

Thank you,

so simple and effective

Dan

 

[T. Valko]

Thank you,

so simple and effective

Dan

You're welcome. Thanks for the feedback!