Need help with COUNTIF Formula

Question

I have 2 Columns, AW and AX

AW Formula:

=SORT(FILTER(AA:AA, AR:AR=1))

AX Formula:

=SORT(FILTER(AB:AB, AR:AR=1))

Context:

AR is a column where customers are flagged

AA is a column where it consist GPS1+Date

AB is a column where it consist GPS1+DATE+GPS2

AB is made that way so that when it gets to AX, it will be ordered the same way as AW

I have a formula in Column AY:

=IF(COUNTIFS(AW:AW, AW6, AX:AX, AX6) = COUNTIF(AW:AW, AW6), 0, 1) 

Column AY condition is if all that are flagged in the same GPS1 and same day, it will check if all GPS2 within that GPS1 and same day is the same then, flag 0, else 1.

My question is, why does it work like this if the rows are matched with other/sorted in AW and AX. If I do this without the sort then it generates incorrect results.

Answer 1

Hi, James Lewel

Thank you for using Microsoft products and posting them to the community.

You can try creating a helper column that generates a sorted index in a new column, such as the AZ column:

=SORT(FILTER(ROW(AA:AA), AR:AR=1))

Use these indexes in the AW column to get the sorted AA column data:

=INDEX(AA:AA, AZ1)

Use these indexes in the AX column to get the sorted AB column data:

=INDEX(AB:AB, AZ1)

Then use your formula for the AY column again.

This should correctly match and calculate the sorted data.

I hope the above information can help you. Feel free to send a message if you need further help.

Best wishes

Aiden.C - MSFT |Microsoft Community Support Specialist

Answer 2

We need to see your (sample) file.
Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

Andreas.