Dynamic Countifs criteria definitions

[myespooir]

Hi there,

I need to count store # that a product goes to based on Countries. I would like to ask how i should structure my countifs formula/ if this is even possible?

=Countifs(Countrylist/range,{Country 1, Country 2} is what do when i have only 2 countries and i know what 2 countries i want to send the product to.

My questions is when
1) I have 5 possible countries that the product goes to,
2）The product goes to only the countries users checked in another table,
how can i make the countifs formula to vary the {Country 1, Country 2,...} part based on user input

Example:

Country 1 Country 2 Country 3 Country 4 Country 5
Product AAA: v v v
Product BBB: v v

how should i structure the countifs formula to recognize, for product AAA, country 1,2,4 are checked, so i should count only stores in these 3 countries in my store list; for product BBB, i need to count stores in country 3 and 5 only in my store list.

Thanks a lot for your help!
Myespooir

 

[Aladin Akyurek]

You have apparently something like:

Row\Col	A​	B​	C​	D​	E​	F​
1​	product/country	Country-1	Country-2	Country-3	Country-4	Country-5
2​	AAA	v	v		v
3​	BBB			v		v

<tbody>
</tbody>

<strike></strike>
What are the required results that the above sample?

 

[myespooir]

Yes! Aladin!! Thanks for the speedy reply！

I will need to base on the user input from the table above to count the Store# in my store list.

My Store list looks like this:

Store code Country
111 Country 1
112 Country 1
113 Country 1
114 Country 2
115 Country 3
116 Country 3
117 Country 4
118 Country 4
119 Country 3
120 Country 5

So product AAA will be sent to 6 stores.
So product BBB will be sent to 4 stores.

 

[Aladin Akyurek]

Yes! Aladin!! Thanks for the speedy reply！

I will need to base on the user input from the table above to count the Store# in my store list.

But what are the results you seek for this exhibit? Just AAA >> 3 and BBB >> 2?

 

[myespooir]

When i countif, i need to know which 3 countries to countif for AAA / 2 countries to countif for BBB

My Store list looks like this:

Store code Country
111 Country 1
112 Country 1
113 Country 1
114 Country 2
115 Country 3
116 Country 3
117 Country 4
118 Country 4
119 Country 3
120 Country 5

=countifs(Storelist,{for product AAA, i count stores in country 1,2,4}
So product AAA will be sent to 6 stores.
=countifs(Storelist,{for product BBB, i count stores in country 3,5}
So product BBB will be sent to 4 stores.

 

[Aladin Akyurek]

Yes! Aladin!! Thanks for the speedy reply！

I will need to base on the user input from the table above to count the Store# in my store list.

My Store list looks like this:

Store code Country
111 Country 1
112 Country 1
113 Country 1
114 Country 2
115 Country 3
116 Country 3
117 Country 4
118 Country 4
119 Country 3
120 Country 5

So product AAA will be sent to 6 stores.
So product BBB will be sent to 4 stores.

Thanks for edit, giving the expected counts...

Row\Col	A​	B​	C​	D​	E​	F​	H​	I​	K​	L​
1​	product/country	Country-1	Country-2	Country-3	Country-4	Country-5	store	country	product	store count
2​	AAA	v	v		v		111​	Country-1	AAA	6​
3​	BBB			v		v	112​	Country-1	BBB	4​
4​							113​	Country-1
5​							114​	Country-2
6​							115​	Country-3
7​							116​	Country-3
8​							117​	Country-4
9​							118​	Country-4
10​							119​	Country-3
11​							120​	Country-5

In L2 control+shift+enter, not just enter, and copy down:

=SUM(IF(ISNUMBER(MATCH($I$2:$I$11,IF(INDEX($B$2:$F$3,
    MATCH(K2,$A$2:$A$3,0),0)="v",$B$1:$F$1),0)),1))

 

[myespooir]

Thanks, This is exactly what i am looking for. But What if I have 6 criteria ranges like this, how should I structure this formula?
in my store list, there are 6 columns to show these 6 criteria, each could contain 5 definitions.
If nothing is checked under the criteria(Eg AAA with Kids/Non-Kids and Vol), I do not need to count this definition in my store list. so for AAA, i need to count based on the first, second, fourth, fifth criteria sets, not the third and sixth.



-- removed inline image ---


Store List

-- removed inline image ---

 

[Aladin Akyurek]

Please try to post your sample directly here on the forum for an image or a link to outside is (often) not useful. Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit.

 

[myespooir]

Got it. Sorry about this. Thanks for your formula! It works!

 

[Aladin Akyurek]

Got it. Sorry about this. Thanks for your formula! It works!

You are welcome. Thanks for the update.