Listing of data with CountIFS formula

[GemBox]

Hello All,

I'm using the 'COUNTIFS' formula to obtain the total number of tickets assigned per region. I aim to extract data based on the result of the 'COUNTIFS' function. I have attached a sample Excel sheet indicating the expected outcome. The list should dynamically adjust based on the selected dates (C2 & E2).

Regards,
Gem

[AliGW]

You cannot extract data using COUNTIFS. Try this in H15:

=FILTER(Data!A2:E113,(Data!B2:B113>=C2)*(Data!B2:B113<=E2)*(Data!D2:D113=C4)*(Data!C2:C113=C5))

Sort ascending:

=LET(f,FILTER(Data!A2:E113,(Data!B2:B113>=C2)*(Data!B2:B113<=E2)*(Data!D2:D113=C4)*(Data!C2:C113=C5)),SORTBY(f,INDEX(f,,5),1))

[GemBox]

Thank you Ali.. I'm not expecting the list to be done through COUNTIFS.. any suitable formula to extract the data..

What if I wanted to display only selected columns.. instead of 5 column data I want only 2 columns (Column A & Column B)

[alansidman]

With Power Query as an alternative

Count by Region and Priority 3

Please Login or Register  to view this content.

for the Tickets

Please Login or Register  to view this content.

Excel 2016 (Windows) 64 bit

	H	I	J	K	L
4	Region	Priority	Count
5	EMEA	P3	6
6	INDIA	P3	4
7	NAM	P3	1
8	SAM	P3	2
9	APAC	P3	2
10
11
12	Ticket No.	Creation date	Priority	Ticket Type	Region
13	CR-A1230439	9/8/2023	P3	CR	INDIA
14	CR-A1230282	9/8/2023	P3	CR	EMEA
15	CR-A1230274	9/8/2023	P3	CR	INDIA
16	CR-A1229885	9/7/2023	P3	CR	EMEA
17	CR-A1229715	9/6/2023	P3	CR	NAM
18	CR-A1229652	9/6/2023	P3	CR	EMEA
19	CR-A1229553	9/6/2023	P3	CR	SAM
20	CR-A1229422	9/6/2023	P3	CR	APAC
21	CR-A1229099	9/5/2023	P3	CR	SAM
22	CR-A1228815	9/4/2023	P3	CR	EMEA
23	CR-A1228556	9/4/2023	P3	CR	INDIA
24	CR-A1228442	9/1/2023	P3	CR	INDIA
25	CR-A1228366	9/1/2023	P3	CR	APAC
26	CR-A1228087	9/1/2023	P3	CR	EMEA

Sheet: Regions

[AliGW]

What if I wanted to display only selected columns.. instead of 5 column data I want only 2 columns (Column A & Column B)

=LET(f,FILTER(Data!A2:E113,(Data!B2:B113>=C2)*(Data!B2:B113<=E2)*(Data!D2:D113=C4)*(Data!C2:C113=C5)),s,SORTBY(f,INDEX(f,,5),1),CHOOSECOLS(s,1,2))

Your title specifically stated that you wanted to list data with COUNTIFS!!!

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[alansidman]

Missed that you wanted this to be dynamic. Have updated the Mcode to recognize the dates shown. The updated code is reflected in the attached file. If you change the dates, then click on Refresh All to update the results.

[GemBox]

@AliGW

Thank you Ali.. Let me check..

[GemBox]

@alansidman

Not working if I change the Priority to P4 or any other priority in C5

[AliGW]

This will deal with no records found:

=LET(f,FILTER(Data!A2:E113,(Data!B2:B113>=C2)*(Data!B2:B113<=E2)*(Data!D2:D113=C4)*(Data!C2:C113=C5)),s,SORTBY(f,INDEX(f,,5),1),IFERROR(CHOOSECOLS(s,1,2),"No Records Found"))

Alan's solution requires you to REFRESH the data (which is why I rarely use PQ these days if Excel 365 is on the table).

[GemBox]

Hello Ali,
Thanks for fine tuning the formula..
I missed to mention one more requirement.. My Column C4 has data validation drop down to select 'CR' or 'Incident' or Blank. If I select CR then CR related data should be listed.. If I select Blank then both CR & as well Incident data should be listed..
Like wise for Column C5 (P1 to P5 and blank)

You response will be highly appreciated..

[AliGW]

You have been a member long enough to know that these critical requirements should NOT be forgotten!

I shall have a nother look, but please STOP quoting unnecessarily!!!

[AliGW]

Try this:

=LET(f,FILTER(Data!A2:E113,(Data!B2:B113>=C2)*(Data!B2:B113<=E2)*IF(C4="",1,Data!D2:D113=C4)*IF(C5="",1,Data!C2:C113=C5)),s,SORTBY(f,INDEX(f,,5),1),IFERROR(CHOOSECOLS(s,1,2),"No Records Found"))

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[AliGW]

Seen, but no response ...

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[GemBox]

Thanks for your help.. It solved my requirement.

[AliGW]

Good to know - thanks.

[alansidman]

Not working if I change the Priority to P4 or any other priority in C5

Your original request only indicated variables for dates. See the attached file which has been amended to make the priority a variable also in the parameter query.

Thanks for the REP.