Countifs and ABS

[nikolaki]

Hi all,

I want to count the cells in column 'F' where the value is >='I2' AND the Absolute value of 'E:E' > 'I7', but some of the cells in 'E:E' contain text.

Excel doesn't like this:
=COUNTIFS(F:F,">="&I2,ABS(E:E),">="&I7).

Any help is deeply appreciated. Thanks much in advance.

 

[Rick Rothstein]

The odd numbered arguments to COUNTIFS are ranges... ABS(E:E) is not a range.

 

[AhoyNC]

Try something like this. It is best not to reference whole columns in your formula.

Book1
	E	F	G	H
2	4	4		7
3	5	5
4	Text	1
5	-1	2
6	-8	0
7	-14	6
Sheet2

Cell Formulas
Range	Formula
H2	H2	=SUM(--(FILTER(ABS(E2:E7),ABS(ISNUMBER(E2:E7)))>=I7),--(F2:F7>=I2))

 

[Alex Blakenburg]

Maybe:

=SUM( IFERROR( (ABS(E2:E7)>I7),0) * (F2:F7>I2) )

 

[AhoyNC]

Alex, I think your formula needs to be changed to:
SUM(--( IFERROR( (ABS(E2:E7)>=I7),0)), --(F2:F7>=I2) )

Book1
	E	F	G	H	I
1
2	4	4		7	4
3	5	5
4	Text	1		7
5	-1	2
6	-8	0		3
7	-14	6			2
Sheet1

Cell Formulas
Range	Formula
H2	H2	=SUM(--(FILTER(ABS(E2:E7),ABS(ISNUMBER(E2:E7)))>=I7),--(F2:F7>=I2))
H4	H4	=SUM(--( IFERROR( (ABS(E2:E7)>=I7),0)), --(F2:F7>=I2) )
H6	H6	=SUM(IFERROR( (ABS(E2:E7)>=I7),0)*(F2:F7>=I2) )

 

[Scott Huish]

=SUMPRODUCT(--(IFERROR(ABS(E2:E7),-1)>=I7),--(F2:F7>=I2))

 

[Alex Blakenburg]

@nikolaki - In your text you have > 'I7' while in your CountIfs you have >= I7 (includes the equal sign), which is correct, you may need to amend all solutions accordingly.
My and Scott's interpretation is that you want to count the cells in Column F that meet both criteria, @AhoyNC interpretation was to add cells in both columns E & F based on independently assessing the criteria in each. Perhaps you can clarify.

Thanks @AhoyNC for your feedback, while my interpretation is different it did make me revisit it and my comparision signs didn't match either the text of the countifs version.
To match the Countifs version both should be >=

=SUM( IFERROR( ABS(E2:E7)>=I7,0) * (F2:F7>=I2) )

@Scott Huish
I opted to put the IFERROR around the whole expression so that the Text #VALUE error made the whole expression return a multiplier of 0.
In your formula, it sets text to -1 which means that if you enter a figure of -1 or less in I7 it will get evaluate as True.
PS: Is there any advantage to using the comma in sumproduct ? If you use multiply instead of the comma you don't need either of the double Unaries "--".

 

[AhoyNC]

Alex, now that I look at it again I think my interpretation was probably wrong.

 

[Phuoc]

Try this:

=COUNTIFS(F:F,">="&I2,E:E,">="&I7) + COUNTIFS(F:F,">="&I2,E:E,"<="&-I7)

 

[Scott Huish]

@nikolaki - In your text you have > 'I7' while in your CountIfs you have >= I7 (includes the equal sign), which is correct, you may need to amend all solutions accordingly.
My and Scott's interpretation is that you want to count the cells in Column F that meet both criteria, @AhoyNC interpretation was to add cells in both columns E & F based on independently assessing the criteria in each. Perhaps you can clarify.

Thanks @AhoyNC for your feedback, while my interpretation is different it did make me revisit it and my comparision signs didn't match either the text of the countifs version.
To match the Countifs version both should be >=

=SUM( IFERROR( ABS(E2:E7)>=I7,0) * (F2:F7>=I2) )

@Scott Huish
I opted to put the IFERROR around the whole expression so that the Text #VALUE error made the whole expression return a multiplier of 0.
In your formula, it sets text to -1 which means that if you enter a figure of -1 or less in I7 it will get evaluate as True.
PS: Is there any advantage to using the comma in sumproduct ? If you use multiply instead of the comma you don't need either of the double Unaries "--".

Long standing opinion on this board is that the -- is faster.
I don't think it should matter if it returns -1 because it would never match an absolute value being greater than I7 because I7 would have to be a positive value for this question to make sense.