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))


[TABLE="width: 450"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Olympic event[/TD]
[TD]Gold[/TD]
[TD]Silver[/TD]
[TD]Bronze[/TD]
[/TR]
[TR]
[TD]Event 1[/TD]
[TD]United Kingdom[/TD]
[TD]Germany[/TD]
[TD]France[/TD]
[/TR]
[TR]
[TD]Event 2[/TD]
[TD]China[/TD]
[TD]Colombia[/TD]
[TD]China[/TD]
[/TR]
[TR]
[TD]Event 3[/TD]
[TD]Cuba[/TD]
[TD]Ukraine[/TD]
[TD]Poland[/TD]
[/TR]
[TR]
[TD]Event 4[/TD]
[TD]Colombia[/TD]
[TD]Colombia[/TD]
[TD]Russia[/TD]
[/TR]
[TR]
[TD]Event 5[/TD]
[TD]Portugal[/TD]
[TD]United States[/TD]
[TD]Indonesia[/TD]
[/TR]
[TR]
[TD]Event 6[/TD]
[TD]United States[/TD]
[TD]China[/TD]
[TD]Peru[/TD]
[/TR]
</tbody>[/TABLE]

 

[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...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

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

1​

[/td][td]Olympic event[/td][td]Gold[/td][td]Silver[/td][td]Bronze[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]Event 1[/td][td]United Kingdom[/td][td]Germany[/td][td]France[/td][td]

7​

[/td][/tr]

[tr][td]

3​

[/td][td]Event 2[/td][td]China[/td][td]Colombia[/td][td]China[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]Event 3[/td][td]Cuba[/td][td]Ukraine[/td][td]Poland[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Event 4[/td][td]Colombia[/td][td]Colombia[/td][td]Russia[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Event 5[/td][td]Portugal[/td][td]United States[/td][td]Indonesia[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Event 6[/td][td]United States[/td][td]China[/td][td]Peru[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]Event 7[/td][td]United States[/td][td]Indonesia[/td][td]Indonesia[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]Event 8[/td][td]Indonesia[/td][td]Germany[/td][td]Indonesia[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]Event 9[/td][td]Indonesia[/td][td]Indonesia[/td][td]France[/td][td][/td][/tr]

[tr][td]

11​

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

[tr][td]

12​

[/td][td]United States[/td][td]

2​

[/td][td]

1​

[/td][td]

0​

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

[tr][td]

13​

[/td][td]Germany[/td][td]

0​

[/td][td]

2​

[/td][td]

0​

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

[tr][td]

14​

[/td][td]France[/td][td]

0​

[/td][td]

0​

[/td][td]

2​

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