ISNUMBER, MATCH, and SUMIFS

[MrOllyR]

I have a range F3:H12. In column F, I have students divided into languages groups, Column G names the groups. Column H says how many students are in each group.

Nationality	Group	Number of students
Italian	Group 1	20​
Italian	Group 2	20​
Italian	Group 3	20​
Italian	Group 4	20​
Italian	Group 5	20​
Arabic	Group 6	20​
Chinese	Group 7	20​
Chinese	Group 8	20​
Spanish	Group 9	20​
		​

Underneath this range I have a list of languages:

Italian

Turkish

Arabic

Chinese

French

Spanish

Portuguese

I want to replace each language in the table above with a formula which counts the total number of students who speak that language.
="Italian: " & SUMIFS(H3:H12,F3:F12, "Italian"). This works and reads, Italian: 100.

I want to add to the formula so if a language does not feature, like Portuguese, it leaves the cell blank. I have made this formula.
=IF(ISNUMBER(MATCH("Portuguese",F3:F12)),("Portuguese: "&SUMIFS(H3:H12,F3:F12,"Portuguese")), "")

It works in that it counts but still displays, Portuguese: , whether there are entries for Portuguese students or not.

Any ideas on how to fix this would be much appreciated.

 

[Marcelo Branco]

Try
=IF(ISNUMBER(MATCH("Portuguese",F$3:F$12,0)),("Portuguese: "&SUMIFS(H$3:H$12,F$3:F$12,"Portuguese")), "")

M.

 

[Herakles]

I have a range F3:H12. In column F, I have students divided into languages groups, Column G names the groups. Column H says how many students are in each group.

Nationality	Group	Number of students
Italian	Group 1	20​
Italian	Group 2	20​
Italian	Group 3	20​
Italian	Group 4	20​
Italian	Group 5	20​
Arabic	Group 6	20​
Chinese	Group 7	20​
Chinese	Group 8	20​
Spanish	Group 9	20​
		​

Underneath this range I have a list of languages:

Italian

Turkish

Arabic

Chinese

French

Spanish

Portuguese

I want to replace each language in the table above with a formula which counts the total number of students who speak that language.
="Italian: " & SUMIFS(H3:H12,F3:F12, "Italian"). This works and reads, Italian: 100.

I want to add to the formula so if a language does not feature, like Portuguese, it leaves the cell blank. I have made this formula.
=IF(ISNUMBER(MATCH("Portuguese",F3:F12)),("Portuguese: "&SUMIFS(H3:H12,F3:F12,"Portuguese")), "")

It works in that it counts but still displays, Portuguese: , whether there are entries for Portuguese students or not.

Any ideas on how to fix this would be much appreciated.

This is what I have come up with.

Put this formula in the first cell in the list and adjust the number 1 in ROW()-1 to equal the row number of the first cell minus 1.

So if this formula is put in row 2 then this would be the formula.

=IFERROR(INDEX(SORT(UNIQUE(Table2[Nationality])),ROW()-1) & " : " & SUM(FILTER(Table2[Number of students],Table2[Nationality]=INDEX(SORT(UNIQUE(Table2[Nationality])),ROW()-1))),"")

ROW()-1 appears twice in the formula.

Copy the formula down until you get a blank value returned. You dont need the formula in this cell.

DatePicker.xlsm
	B	C	D	E	F	G
1
2	Nationality	Group	Number of students			Arabic : 20
3	Italian	Group 1	20			Chinese : 40
4	Italian	Group 2	20			Italian : 100
5	Italian	Group 3	20			Spanish : 20
6	Italian	Group 4	20
7	Italian	Group 5	20
8	Arabic	Group 6	20
9	Chinese	Group 7	20
10	Chinese	Group 8	20
11	Spanish	Group 9	20
12
SumInTableVersion1

Cell Formulas
Range	Formula
G2:G5	G2	=INDEX(SORT(UNIQUE(Table2[Nationality])),ROW()-1) & " : " & SUM(FILTER(Table2[Number of students],Table2[Nationality]=INDEX(SORT(UNIQUE(Table2[Nationality])),ROW()-1)))

 

[Herakles]

This is what I have come up with.

Put this formula in the first cell in the list and adjust the number 1 in ROW()-1 to equal the row number of the first cell minus 1.

So if this formula is put in row 2 then this would be the formula.

=IFERROR(INDEX(SORT(UNIQUE(Table2[Nationality])),ROW()-1) & " : " & SUM(FILTER(Table2[Number of students],Table2[Nationality]=INDEX(SORT(UNIQUE(Table2[Nationality])),ROW()-1))),"")

ROW()-1 appears twice in the formula.

Copy the formula down until you get a blank value returned. You dont need the formula in this cell.

DatePicker.xlsm
	B	C	D	E	F	G
1
2	Nationality	Group	Number of students			Arabic : 20
3	Italian	Group 1	20			Chinese : 40
4	Italian	Group 2	20			Italian : 100
5	Italian	Group 3	20			Spanish : 20
6	Italian	Group 4	20
7	Italian	Group 5	20
8	Arabic	Group 6	20
9	Chinese	Group 7	20
10	Chinese	Group 8	20
11	Spanish	Group 9	20
12
SumInTableVersion1

Cell Formulas
Range	Formula
G2:G5	G2	=INDEX(SORT(UNIQUE(Table2[Nationality])),ROW()-1) & " : " & SUM(FILTER(Table2[Number of students],Table2[Nationality]=INDEX(SORT(UNIQUE(Table2[Nationality])),ROW()-1)))

You will need to change Table2 to the name of your table.

The list is in alphabetical order.