Download "testformula modified.xlsx" from
https://www.box.com/s/269a09e4ff285e8b8c7c.
See the "modified" worksheet. Be sure to read the textboxes on the right.
In a nutshell....
I have inserted columns F through H to demonstrate the formulas.
Column F is my original SUMIF formula.
Column G and H are SUMIFS (with an "s") and SUMPRODUCT alternatives that
obviate the need for Column C, if you prefer.
Since you are using Excel 2007 or later, SUMIFS is preferred. But if you
need compatibility with Excel 2003, use SUMPRODUCT.
The formulas are basically no different than the ones suggested in this
thread already. The only difference is that I extended the ranges, which
might be part of the problem you are having in applying the suggestions.
I also noted a mistake in one of your "result" formula, originally in F4,
now in I4.
Also note that the file you uploaded has Manual calculation mode set.
I left that as-is. But that might create some confusion when you copy
formulas. They might not be recalculated until you manually calculate by
pressing F9. Is that intentional?
One final comment.... I notice that column D are rounded to integers due to
formatting. And the ratios in columns F through H are rounded to 2 decimal
places due to formatting, which matches your original column F, now column
I.
Consequently, the ratios are deceptively equal in some cases; and their
displayed values might not sum to 1.00.
You probably understand the consequences of your format choices. But I
thought I would bring that to your attention, just in case you do not.