Counting with multiple OR criteria

[Excel_12]

Hello,


I've been puzzled by this situation for quite some time now... Below is partial data set and i need a formula where i have to count "In how many events did either the US, Germany or France win a medal?"

This makes it a bit more difficult than usual for sumproduct because of multiple OR criteria in the same ranges.

i tried this formula but i wasnt getting the right answer..

=SUMPRODUCT(--(((($M$6:$M$101)={"United States","Germany","France"})+(($N$6:$N$101)={"United States","Germany","France"})+(($M$6:$M$101)={"United States","Germany","France"}))>0))

Olympic event	Gold	Silver	Bronze
Event 1	United Kingdom	Germany	France
Event 2	China	Colombia	China
Event 3	Cuba	Ukraine	Poland
Event 4	Colombia	Colombia	Russia
Event 5	Portugal	United States	Indonesia
Event 6	United States	China	Peru

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[chullan88]

Hi,

Use the following formula in E2 and pull down.

=SUMPRODUCT(COUNTIF(B2:D2,{"United States","Germany","France"}))

 

[AhoyNC]

Try:
Change ranges to match your data.

Excel Workbook
	A	B	C	D	E	F
1	Olympic event	Gold	Silver	Bronze	*	*
2	Event 1	United Kingdom	Germany	France	*	4
3	Event 2	China	Colombia	China	*	*
4	Event 3	Cuba	Ukraine	Poland	*	*
5	Event 4	Colombia	Colombia	Russia	*	*
6	Event 5	Portugal	United States	Indonesia	*	*
7	Event 6	United States	China	Peru	*	*
Sheet

 

[Tetra201]

Try this for your partial dataset:

=SUMPRODUCT(COUNTIF(B2:D7,{"United States","Germany","France"}))

 

[Excel_12]

None of the above solve my issue. The closest i got was with my formula... this isn't meant to be easy due to the fact that were looking multiple or criteria in multiple criteria ranges

 

[AhoyNC]

Based on your example, what result do you expect to see?

 

[FDibbins]

Try this...

	A​	B​	C​	D​	E​
1​	Olympic event	Gold	Silver	Bronze
2​	Event 1	United Kingdom	Germany	France	7​
3​	Event 2	China	Colombia	China
4​	Event 3	Cuba	Ukraine	Poland
5​	Event 4	Colombia	Colombia	Russia
6​	Event 5	Portugal	United States	Indonesia
7​	Event 6	United States	China	Peru
8​	Event 7	United States	Indonesia	Indonesia
9​	Event 8	Indonesia	Germany	Indonesia
10​	Event 9	Indonesia	Indonesia	France
11​
12​	United States	2​	1​	0​
13​	Germany	0​	2​	0​
14​	France	0​	0​	2​

E2=SUM(COUNTIF($B$2:$D$10,{"United States","Germany","France"}))

A12:D14 shows a summary by country and type, for comparison

 

[Excel_12]

This is how you do it with a helper column i need it to be a one cell solution, thanks... i wish i could attach the file

						Criteria Q2
						United States
Olympic event	Gold	Silver	Bronze	Q2		Germany
1	United Kingdom	Germany	France	=MIN(1,(COUNTIF(C6:E6,$H$4)+COUNTIF(C6:E6,$H$5)+COUNTIF(C6:E6,$H$6)))		France
=B6+1	China	Colombia	China	=MIN(1,(COUNTIF(C7:E7,$H$4)+COUNTIF(C7:E7,$H$5)+COUNTIF(C7:E7,$H$6)))
=B7+1	Cuba	Ukraine	Poland	=MIN(1,(COUNTIF(C8:E8,$H$4)+COUNTIF(C8:E8,$H$5)+COUNTIF(C8:E8,$H$6)))		Question 2:	In how many events did either the US, Germany or France win a medal?	=SUM(F6:F101)
=B8+1	Colombia	Colombia	Russia	=MIN(1,(COUNTIF(C9:E9,$H$4)+COUNTIF(C9:E9,$H$5)+COUNTIF(C9:E9,$H$6)))
=B9+1	Portugal	United States	Indonesia	=MIN(1,(COUNTIF(C10:E10,$H$4)+COUNTIF(C10:E10,$H$5)+COUNTIF(C10:E10,$H$6)))
=B10+1	United States	China	Peru	=MIN(1,(COUNTIF(C11:E11,$H$4)+COUNTIF(C11:E11,$H$5)+COUNTIF(C11:E11,$H$6)))
=B11+1	Indonesia	Vietnam	Croatia	=MIN(1,(COUNTIF(C12:E12,$H$4)+COUNTIF(C12:E12,$H$5)+COUNTIF(C12:E12,$H$6)))
=B12+1	Philippines	Tunisia	Indonesia	=MIN(1,(COUNTIF(C13:E13,$H$4)+COUNTIF(C13:E13,$H$5)+COUNTIF(C13:E13,$H$6)))
=B13+1	United States	China	Comoros	=MIN(1,(COUNTIF(C14:E14,$H$4)+COUNTIF(C14:E14,$H$5)+COUNTIF(C14:E14,$H$6)))
=B14+1	Russia	United States	Poland	=MIN(1,(COUNTIF(C15:E15,$H$4)+COUNTIF(C15:E15,$H$5)+COUNTIF(C15:E15,$H$6)))
=B15+1	Poland	China	Philippines	=MIN(1,(COUNTIF(C16:E16,$H$4)+COUNTIF(C16:E16,$H$5)+COUNTIF(C16:E16,$H$6)))
=B16+1	United States	South Korea	Brazil	=MIN(1,(COUNTIF(C17:E17,$H$4)+COUNTIF(C17:E17,$H$5)+COUNTIF(C17:E17,$H$6)))
=B17+1	Russia	Colombia	Germany	=MIN(1,(COUNTIF(C18:E18,$H$4)+COUNTIF(C18:E18,$H$5)+COUNTIF(C18:E18,$H$6)))
=B18+1	Indonesia	China	Venezuela	=MIN(1,(COUNTIF(C19:E19,$H$4)+COUNTIF(C19:E19,$H$5)+COUNTIF(C19:E19,$H$6)))
=B19+1	Portugal	Philippines	Indonesia	=MIN(1,(COUNTIF(C20:E20,$H$4)+COUNTIF(C20:E20,$H$5)+COUNTIF(C20:E20,$H$6)))
=B20+1	Israel	Ethiopia	Libya	=MIN(1,(COUNTIF(C21:E21,$H$4)+COUNTIF(C21:E21,$H$5)+COUNTIF(C21:E21,$H$6)))
=B21+1	Indonesia	Kazakhstan	Peru	=MIN(1,(COUNTIF(C22:E22,$H$4)+COUNTIF(C22:E22,$H$5)+COUNTIF(C22:E22,$H$6)))
=B22+1	China	Georgia	El Salvador	=MIN(1,(COUNTIF(C23:E23,$H$4)+COUNTIF(C23:E23,$H$5)+COUNTIF(C23:E23,$H$6)))
=B23+1	Brazil	Bulgaria	United Kingdom	=MIN(1,(COUNTIF(C24:E24,$H$4)+COUNTIF(C24:E24,$H$5)+COUNTIF(C24:E24,$H$6)))
=B24+1	Indonesia	Morocco	China	=MIN(1,(COUNTIF(C25:E25,$H$4)+COUNTIF(C25:E25,$H$5)+COUNTIF(C25:E25,$H$6)))
=B25+1	China	Ukraine	China	=MIN(1,(COUNTIF(C26:E26,$H$4)+COUNTIF(C26:E26,$H$5)+COUNTIF(C26:E26,$H$6)))
=B26+1	China	China	China	=MIN(1,(COUNTIF(C27:E27,$H$4)+COUNTIF(C27:E27,$H$5)+COUNTIF(C27:E27,$H$6)))
=B27+1	United States	China	China	=MIN(1,(COUNTIF(C28:E28,$H$4)+COUNTIF(C28:E28,$H$5)+COUNTIF(C28:E28,$H$6)))
=B28+1	Indonesia	China	China	=MIN(1,(COUNTIF(C29:E29,$H$4)+COUNTIF(C29:E29,$H$5)+COUNTIF(C29:E29,$H$6)))
=B29+1	Armenia	France	Colombia	=MIN(1,(COUNTIF(C30:E30,$H$4)+COUNTIF(C30:E30,$H$5)+COUNTIF(C30:E30,$H$6)))

<colgroup><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>