Forum Discussion
Formula Sum Help
beefykenny Normally I would recommend SUMIF, but all of the numeric values in your screenshot appear to be numbers formatted as text, so SUMIF won't work here. As an alternative, you could try SUMPRODUCT with the VALUE function to coerce the text values in column G to numeric values. For example, in cell G11 use the following formula, then copy/drag it down to cell G13:
=SUMPRODUCT(($E$2:$E$9=E11)*VALUE($G$2:$G$9))
Daniel_H1190 I've never used Google Sheets, so can't really comment about why it works there. The reason SUMIF won't work in Excel for this situation is that the [sum_range] contains text values (numbers stored as text). I made this assumption because of the left-aligned numbers with green error flags in the top-left corner of each cell (shown in the screenshot provided by the OP). As a result, =SUMIF($E$2:$E$9, E11, $G$2:$G$9) will return 0.
Also, Excel formulas like SUMIF, COUNTIF, AVERAGEIF, etc. will only accept a physical range reference for each of the range parameters. As soon as you perform any calculation on a range, such as VALUE($G$2:$G$9), it will produce an array of results, which is not accepted by these types of functions. SUMPRODUCT, on the other hand, is designed specifically to work with arrays.