Forum Discussion
Formula Sum Help
I am looking for a formula that will get the sum of each unique item's quantity in a column and put that output in a chosen cell.
I currently have this:
As you can see, the set of item names is beneath the item names column and each item's quantity sum is beneath the
item quantities column.
Currently, the item qty sums are hardcoded. I want to dynamically do this with a formula. How do I do that?
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))
5 Replies
- =sum(take(unique(filter(c2:g9,e2:e9=e11,0)),,-1))
- This function isn't valid 🤔
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))- I couldn't get SUMIF to work in Excel but when I moved over to Google Sheets, it worked fine for this problem. Why is that?
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.
