I have a large spreadsheet of business counts across hundreds of small regions (see link). I have used the following formula to try and identify all rows with the relevant name (e.g. Abbotsford) and sum the total business column:
=SUMIF($C$3:$C$46022,F4,INDEX($D$3:$D$46022,MATCH(F4,$C$3:$C$46022,0),0))
Unfortunately, while the total for the first region is correct, the remaining regions either significantly undercount the actual total or return the value 0.
I attempted the following alternative formula (per this solution), but it also does not return the correct total:
=SUMPRODUCT(SUMIF(C$3:C$46022,INDEX(F$4:F$24,MATCH(F5,F5,0),0),D$3:D$46022))