Quartile with Multiple criteria without using Array

[Siops]

Hi, I have big amount of data and I am trying to do a Quartile formula with multiple condition. My challenge right now is if I use the formula using array, my worksheet takes a lot of time to complete the formula. Is there an alternative formula to do this? Please help. Thank you in advance.

A= Criteria1 Range
B= Criteria2 Range
C= Values

[Limor_OP]

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[Siops]

Hi Belida, I attached the sample file but in reality my data is around 50,000 rows that's why I am having problem using the array formula as it takes a lot of time to calculate the formula.

[Limor_OP]

=SUMIFS(C2:C11,A2:A11,"10/1/2020",B2:B11,"Appointment")

[Siops]

That is SUM formula. Where's the quartile?

[Bo_Ry]

This requires an array calculation if Column A and B are not sorted.

Original formula
=QUARTILE(IF($B:$B=B2,IF($A:$A=A2,$C:$C)),1) calcuatate 3 Million time per 1 cell

Use limited row to reduce the calculation to 150k time would help with speed.

=QUARTILE(IF($B$2:$B$50000=B2,IF($A$2:$A$50000=A2,$C$2:$C$50000)),1)


Without array calculation, Column A and B need to be sorted and use a helper column.
E2
=A2&B2

F2
=QUARTILE(INDEX(C:C,MATCH(E2,E:E,)):INDEX(C:C,MATCH(E2,E:E)),1)

[Siops]

Bo_Ry, this works! but how do I exclude in quartile calculation if the values is either blank or 0?

[Bo_Ry]

Try
=QUARTILE(IF($B$2:$B$50000=B2,IF($A$2:$A$50000=A2,IF($C$2:$C$50000,$C$2:$C$50000))),1)

[Siops]

Thanks a lot, Bo_Ry