Dynamic Countifs criteria definitions

myespooir · Mar 27, 2016

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 · Mar 27, 2016

You have apparently something like:

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A

[/TD]
[TD]

B

[/TD]
[TD]

C

[/TD]
[TD]

D

[/TD]
[TD]

E

[/TD]
[TD]

F

[/TD]
[/TR]
[TR]
[TD]

1

[/TD]
[TD]product/country[/TD]
[TD]Country-1[/TD]
[TD]Country-2[/TD]
[TD]Country-3[/TD]
[TD]Country-4[/TD]
[TD]Country-5[/TD]
[/TR]
[TR]
[TD]

2

[/TD]
[TD]AAA[/TD]
[TD]v[/TD]
[TD]v[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3

[/TD]
[TD]BBB[/TD]
[TD][/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD]v[/TD]
[/TR]
</tbody>[/TABLE]

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

myespooir · Mar 27, 2016

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 · Mar 27, 2016

myespooir said:
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 · Mar 27, 2016

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 · Mar 27, 2016

myespooir said:
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...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A

[/td][td]

B

[/td][td]

C

[/td][td]

D

[/td][td]

E

[/td][td]

F

[/td][td]

H

[/td][td]

I

[/td][td]

K

[/td][td]

L

[/td][/tr]
[tr][td]

1

[/td][td]product/country[/td][td]Country-1[/td][td]Country-2[/td][td]Country-3[/td][td]Country-4[/td][td]Country-5[/td][td]store[/td][td]country[/td][td]product[/td][td]store count[/td][/tr]

[tr][td]

2

[/td][td]AAA[/td][td]v[/td][td]v[/td][td][/td][td]v[/td][td][/td][td]

111

[/td][td]Country-1[/td][td]AAA[/td][td]

6

[/td][/tr]

[tr][td]

3

[/td][td]BBB[/td][td][/td][td][/td][td]v[/td][td][/td][td]v[/td][td]

112

[/td][td]Country-1[/td][td]BBB[/td][td]

4

[/td][/tr]

[tr][td]

4