Sort Unique Filter and Countifs

[searchingforhelp]

Hi, I hope all is well.

Need assistance with two separate formulas

1. There are a total of four locations listed in column A. I am trying to separate the locations and identify the theme with that location, and the number of times it occurred in a specified month. I am only able to identify the theme in column F and the monthly occurrence in column G, H and I but not the location. Looking for a formula that will provide me the location with the theme and column G, H and I. Something similar to the attached image if applicable. Please note the list is longer in Column A, in case you are wondering why the numbers are not adding up

2. Is there a way to simplify the formula in column G, H and I from the data listed in column C rather than using column D?


. .

Mr Excel.xlsx
	A	B	C	D	E	F	G	H	I
1	Location	Theme	Dates	Month		Theme	May	June	July
2	Hawaii	Flowers	7/28/2022	Jul		Day Cruises	0	2	1
3	California	Sequoia sempervirens	5/7/2022	May		Fishing	0	1	0
4	Hawaii	Music	7/4/2022	Jul		Flowers	0	0	1
5	Maine	Lobster	6/1/2022	Jun		Grand Canyon	1	1	0
6	Hawaii	Helicoptor tour	5/17/2022	May		Helicoptor tour	2	0	0
7	Arizona	Grand Canyon	6/19/2022	Jun		Lobster	0	1	1
8	Maine	Lobster	7/9/2022	Jul		Luau	1	0	0
9	Hawaii	Luau	5/10/2022	May		Music	0	0	1
10	Maine	Fishing	6/7/2022	Jun		Navajo	1	0	0
11	Hawaii	Helicoptor tour	5/12/2022	May		Red Mountains	0	1	0
12	California	Surfing	7/4/2022	Jul		Sequoia sempervirens	1	0	1
13	Arizona	Navajo	5/7/2022	May		Surfing	0	0	1
14	Maine	Day Cruises	6/17/2022	Jun		Swimming	1	0	0
Sheet1

Cell Formulas
Range	Formula
F2:F14	F2	=SORT(UNIQUE(FILTER(B2:B6000,B2:B6000<>"")))
G2:G14	G2	=COUNTIFS(B:B,F2#,D:D,"May")
H2:H14	H2	=COUNTIFS(B:B,F2#,D:D,"Jun")
I2:I14	I2	=COUNTIFS(B:B,F2#,D:D,"Jul")
D2:D14	D2	=CHOOSE((MONTH(C2)),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
Dynamic array formulas.

 

[Takae]

E2=SORT(UNIQUE(FILTER(A2:B6000,(A2:A6000<>"")*(B2:B6000<>""))))
It will spill to column F.
If you change the formulas on Column D to "month(D2")", G2 will be set "=COUNTIFS($A:$A,$E2,$B:$B,$F2,$D:$D,COLUMN()-2)"

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Location	Theme	Dates	Month			Theme	May	June	July
2	Hawaii	Flowers	28/07/2022	Jul		Arizona	Grand Canyon	0	1	0
3	California	Sequoia sempervirens	07/05/2022	May		Arizona	Navajo	1	0	0
4	Hawaii	Music	04/07/2022	Jul		California	Sequoia sempervirens	1	0	0
5	Maine	Lobster	01/06/2022	Jun		California	Surfing	0	0	1
6	Hawaii	Helicoptor tour	17/05/2022	May		Hawaii	Flowers	0	0	1
7	Arizona	Grand Canyon	19/06/2022	Jun		Hawaii	Helicoptor tour	2	0	0
8	Maine	Lobster	09/07/2022	Jul		Hawaii	Luau	1	0	0
9	Hawaii	Luau	10/05/2022	May		Hawaii	Music	0	0	1
10	Maine	Fishing	07/06/2022	Jun		Maine	Day Cruises	0	1	0
11	Hawaii	Helicoptor tour	12/05/2022	May		Maine	Fishing	0	1	0
12	California	Surfing	04/07/2022	Jul		Maine	Lobster	0	1	1
13	Arizona	Navajo	07/05/2022	May
14	Maine	Day Cruises	17/06/2022	Jun
Report

Cell Formulas
Range	Formula
F2:G12	F2	=SORT(UNIQUE(FILTER(A2:B6000,A2:A6000<>"")),{1,2})
H2:J12	H2	=COUNTIFS($A:$A,INDEX($F2#,,1),$B:$B,INDEX($F2#,,2),$D:$D,LEFT(H1,3))
D2:D14	D2	=TEXT(C2,"mmm")
Dynamic array formulas.

 

[searchingforhelp]

E2=SORT(UNIQUE(FILTER(A2:B6000,(A2:A6000<>"")*(B2:B6000<>""))))
It will spill to column F.
If you change the formulas on Column D to "month(D2")", G2 will be set "=COUNTIFS($A:$A,$E2,$B:$B,$F2,$D:$D,COLUMN()-2)"

Hi Takae and Fluff, thank you for the assistance. I noticed the data has to be exact in order for the formula to work. What would help is if the formula can find the columns and provide the same data. If there is data in column B, the formula is capturing column A, B and C resulting in a spill (See example one). I need the formula to capture wherever the column is, in the sheet. The second issue is similar to the first. If there is data between the formula for the months (e.g., column E, F) the formula in column I, J and K is incorrect (see example two). I need the formula to capture wherever the column is, in the sheet.

I hope this is exampled clearly and thank you for your help

 

[Fluff]

In that case can you please post some realistic data for what you actually have.

 

[searchingforhelp]

Thank you, team, I was able to adjust the formulas .

 

[Fluff]

Glad you sorted it & thanks for the feedback.