Counting absolute values in range

**Muammar** · 09-10-2012, 04:34 AM

Hi All,

I am working on counting data within a range of values. I have both negative and positive values and want my formula to only pick absolute values. I have tried both of the formulas below but none seems to work.

=COUNTIFS(B2:B38,"<="&ABS(Parameters!B1),B2:B38,">="&ABS(Parameters!B2))

=COUNTIFS(B2:B38,"<="&Parameters!B1,Data!B2:B38,">="&Parameters!B2,B2:B38,">="&-Parameters!B1,B2:B38,"<="&-Parameters!B2)
Book 1.xls
Attached is a workbook where I am using the formulas, please help.

Kindest Regards
Mo

**Ace_XL** · 09-10-2012, 04:45 AM

Use..

=SUM(COUNTIFS(B2:B38,"<="&Parameters!B1,Data!B2:B38,">="&Parameters!B2),COUNTIFS(B2:B38,">="&-Parameters!B1,B2:B38,"<="&-Parameters!B2))

**JosephP** · 09-10-2012, 04:54 AM

why should the result be 36 and not 24 if you are counting absolute values? (there are several that would be outside the 100m-1000m range on an absolute basis) if it should be 24
=SUMPRODUCT((ABS(B2:B38)<=Parameters!B1)*(ABS(B2:B38)>=Parameters!B2))

**Muammar** · 09-10-2012, 05:12 AM

Thanks Ace and Joseph both your formulas worked. And Joseph your right the figure should be 24, my mistake.

**Muammar** · 09-10-2012, 05:14 AM

Thanks Dilipandey. I saw that after putting in the formulas.

**dilipandey** · 09-10-2012, 05:02 AM

Hi Muammar,

According to me the result should be 24, see the yellow cells on the right side in the attached file:0
Book 1.xls

Regards,
DILIPandey

<click on below 'star' if this helps>