Excel function: countifs with nested OR function and wildcards

Hi, can anyone help please.

My formula keeps returning an #VALUE error.

I will attach a sample data (different from my original but is simplified and will do the job).
I put the formula and formulated my question on it.

Please beware I am not very advanced on Excel so I don't understand anything too complicated :-)

Thank you!

Thanks Pepe, you might have a point. I just wonder if there is a way without changing the format simply because I have a much larger data set and sorting through the combination of each city/collection would be really time consuming. Is that the only way?

F2=SUMPRODUCT((ISNUMBER(MATCH($A2:$C2,$I$2:$I$3,0)))*(ISNUMBER(SEARCH($H$2,$D2)))*(ISNUMBER(SEARCH($H$3,$D2))))

Copy down

It is the best way, as you will continually be putting fixes in the data. It would be good to have model answers for the current sample. you are only looking at 1 row
=COUNTIFS($A2:$C2,OR("*Sy*City*","Sydney*"),$D2,"bags*Accessories*")

is it if(d2="*Accessories*",sum(COUNTIFS($A2:$C2,{"*Sy*City*","Sydney*"})),"")

things are hard coded and you are continually applying fixes for mis typing. Always better to standardised or clean at the source

Caracalla it seems to be working, thank you!! And what a complex formula....wonder if I can sneak you in to the office haha don't go far ;-)

Davsth and kvsrinivasamurthy thank you too for your help/tips :-)