0

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))
2
  • What is the reason you have added INDEX MATCH in your SUMIF? Have you tried =SUMIF($C$3:$C$46022,F4,$D$3:$D$46022)
    – patkim
    Mar 2, 2017 at 4:13
  • Inexperience essentially! I just tried your alternative formula and it works perfectly - thanks Mar 2, 2017 at 4:21

1 Answer 1

1

Thanks to pat2015 for providing a solution that solved this problem. It appears that there is no need to have INDEX or MATCH in the formula:

=SUMIF($C$3:$C$46022,F4,$D$3:$D$46022)

It also appears that SUMPRODUCT can provide a correct response, as per the following formula that provided on this website:

=SUMPRODUCT(--($C$3:$C$46022=F4),$D$3:$D$46022)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .