Let's break down what's going wrong with your formula and fix it to get the expected 950 total for Bunbury between 06-05-17 and 08-05-17.

The Correct Formula #

First, here's the working formula you should use:

=SUMIFS(INDEX($B$16:$BC$21, 0, MATCH("Bunbury", $A$15:$BC$15, 0)), $A$16:$A$21, ">=" & $I3, $A$16:$A$21, "<=" & $J3)

Why Your Original Formula Threw #VALUE! #

Your original formula had a critical misusage of the INDEX function:

=SUMIFS(INDEX(B:G,MATCH("Bunbury",$A15:$BC15,0),0),$A$16:$A$21,"<=" & $J3,$A$16:$A$21,">=" & $I3)

• The INDEX function syntax is INDEX(range, row_num, column_num). Your MATCH returns the column number of "Bunbury" in row 15, but you passed that number as the row_num argument to INDEX(B:G, ..., 0). This meant you were pulling an entire row from B:G instead of the column where Bunbury's values live—SUMIFS can't process this invalid range, hence the #VALUE! error.
• You also used mixed references ($A15:$BC15) instead of absolute references ($A$15:$BC$15), which could cause issues if you ever drag the formula to other cells.

Key Checks to Ensure It Works #

• Date Order: Double-check that $I3 is the earlier date (06-05-17) and $J3 is the later date (08-05-17). If you have them reversed, your conditions will cancel each other out and return 0 instead of 950.
• Cell Formats: Make sure cells $A$16:$A$21, $I3, and $J3 are formatted as dates (not plain text). Text dates won't compare correctly with actual date values.
• Range Accuracy: Confirm $B$16:$BC$21 covers all the numerical values for your centers, and $A$15:$BC$15 includes all center names (including Bunbury).

内容的提问来源于stack exchange，提问作者Tanim