Group by and sumproduct in Excel

Question

I want to group by the values in one column based on another and then use the sumproduct (all in one formula, without additional column).

Here is my example table. Unfortunately I don't know how to create table, but here is the link to the image of table: https://ibb.co/9pLcQ89

Country M/F number of people  
US      M   50  
US      F   40  
UK      M   75  
UK      F   60  

I want to get the following result. First to group by by country:

US = 90
UK = 135

The next step is to use SUMPRODUCT: 90*90+135*135=26325

And finally my result should be: SQRT(26325)=162.2498

My biggest concern is how to get the group numbers using one formula without using the names US and UK?

Thanks!

Answer 1

This array formula will create the unique list and do the math:

=SQRT(SUMPRODUCT(SUMIF(A:A,INDEX(A:A,N(IF({1},MODE.MULT(IF(ROW(A2:A5)=MATCH(A2:A5,A:A,0),ROW(A2:A5)*{1,1}))))),C:C)^COUNTIF(A:A,INDEX(A:A,N(IF({1},MODE.MULT(IF(ROW(A2:A5)=MATCH(A2:A5,A:A,0),ROW(A2:A5)*{1,1}))))))))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

---

By changing the references it does not matter the number of countries, the range will grow as needed:

=SQRT(SUMPRODUCT(SUMIF(A:A,INDEX(A:A,N(IF({1},MODE.MULT(IF(ROW(A2:INDEX(A:A,MATCH("zzz",A:A)))=MATCH(A2:INDEX(A:A,MATCH("zzz",A:A)),A:A,0),ROW(A2:INDEX(A:A,MATCH("zzz",A:A)))*{1,1}))))),C:C)^COUNTIF(A:A,INDEX(A:A,N(IF({1},MODE.MULT(IF(ROW(A2:INDEX(A:A,MATCH("zzz",A:A)))=MATCH(A2:INDEX(A:A,MATCH("zzz",A:A)),A:A,0),ROW(A2:INDEX(A:A,MATCH("zzz",A:A)))*{1,1}))))))))

Answer 2

You can use an array formula (entered with CTRL+SHIFT+ENTER):

=SQRT((SUMPRODUCT(IF(A2:A5="US",C2:C5))^COUNTIF(A2:A5,"US"))+(SUMPRODUCT(IF(A2:A5="UK",C2:C5)) ^COUNTIF(A2:A5,"UK")))