How to use COUNTIF for >= alongside other COUNTIF functions

Hi,

I'm Sneha and I'd be happy to help you out with your question.

To use the COUNTIF function with a criteria that involves a comparison operator like ">=", you need to use the criteria argument as a string. You can do this by enclosing the criteria in quotation marks and using the concatenation operator (&) to combine the operator with the cell reference for the value you want to compare to.

In your case, you can modify the COUNTIF function as follows:

COUNTIF('Raw Data'!$J$2:$J$1585, ">=" & 'NOTES'!$I$5)

This will count the number of cells in the range 'Raw Data'!$J$2:$J$1585 that are greater than or equal to the value in cell 'NOTES'!$I$5.

You can then include this COUNTIF function in your formula as follows:

INDEX('Raw Data'!$A$2:$W$1585, SMALL( IF(COUNTIF(NOTES!$I$3, 'Raw Data'!$G$2:$G$1585) * COUNTIF(NOTES!$I$4, 'Raw Data'!$D$2:$D$1585) * COUNTIF('Raw Data'!$J$2:$J$1585, ">=" & 'NOTES'!$I$5), MATCH(ROW('Raw Data'!$A$2:$W$1585),ROW('Raw Data'!$A$2:$W$1585))), ROWS($A$1:D83)),COLUMNS($A$1:D83))

This should allow you to use the COUNTIF function with a criteria involving the ">=" operator alongside the other COUNTIF functions in your formula.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help to the best of my ability.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Sneha

Hi there

@CG_369

@Miss Sneha Gupta

As far as I know,

The COUNTIF function Syntax =COUNTIF(range, criteria)

Where Arguments

range - The range of cells to count.

criteria - The criteria that control which cells should be counted in the given range

Your current COUNTIF formulas show the opposite

COUNTIF(NOTES!$I$3, 'Raw Data'!$G$2:$G$5011)

The range should be 'Raw Data'!$G$2:$G$5011

The criteria should be NOTES!$I$3

So the correct formula Syntax should be COUNTIF('Raw Data'!$G$2:$G$5011,NOTES!$I$3)

The final formula should be

=INDEX('Raw Data'!$A$2:$W$5011,

SMALL(

IF(

COUNTIF('Raw Data'!$G$2:$G$5011,NOTES!$I$3)

*COUNTIF('Raw Data'!$D$2:$D$5011,NOTES!$I$4)

*COUNTIF('Raw Data'!$J$2:$J$5011,">="&NOTES!$I$5),

MATCH(ROW('Raw Data'!$A$2:$W$5011),ROW('Raw Data'!$A$2:$W$5011))),

ROWS($A$1:J10)),COLUMNS($A$1:J10)),"")

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Hi,

I'm Sneha and I'd be happy to help you out with your question. Sorry for the inconvenience caused.

You can include this COUNTIF function in your formula as follows:

=INDEX('Raw Data'!$A$2:$W$5011, SMALL( IF(COUNTIF(NOTES!$I$3, 'Raw Data'!$G$2:$G$5011)

COUNTIF(NOTES!$I$4, 'Raw Data'!$D$2:$D$5011) COUNTIF('Raw Data'!$J$2:$J$5011, ">=" & 'NOTES'!$I$5), MATCH(ROW('Raw Data'!$A$2:$W$5011),ROW('Raw Data'!$A$2:$W$5011))), ROWS($A$1:J10)),COLUMNS($A$1:J10))

This should allow you to use the COUNTIF function with a criteria involving the ">=" operator alongside the other COUNTIF functions in your formula.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help to the best of my ability.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Sneha

Hi Sneha,

Thank you so much for the help and explanation too.

It was working for a while but it isn't anymore and just disregarding the >=285

=INDEX('Raw Data'!$A$2:$W$5011,

SMALL(

IF(

COUNTIF(NOTES!$I$3, 'Raw Data'!$G$2:$G$5011)

*COUNTIF(NOTES!$I$4, 'Raw Data'!$D$2:$D$5011)

*COUNTIF('Raw Data'!$J$2:$J$5011,">="&NOTES!$I$5),

MATCH(ROW('Raw Data'!$A$2:$W$5011),ROW('Raw Data'!$A$2:$W$5011))),

ROWS($A$1:J10)),COLUMNS($A$1:J10)),"")

Is there something more to add or another function I should add in?

Thank you very much, your help is highly appreciated :)

CG

I didn't do any attempt trying to understand or test your formula, but do note that the syntax for the COUNTIF part is incorrect.

The function expects COUNTIF(range, criteria) whereas you wrote it as COUNTIF(criteria, range).