Forum Discussion
Formula to Sum Column referencing another Column based on Criteria
I am trying to sum the values of one column by referencing another column that has repetitive criteria (similar to a Pivot but with a formula instead).
For example, in column A "MGMTCO001" is listed 3 times (A8, A9, A10). I want to obtain the sum of cells P8, P9, and P10 that are in the corresponding rows. So, I would get the result of 600 for "MGMTCO001" in column R for each row.
However, I do not want to specifically list "MGMTCO001" in the formula because the value in column A will change. Please note that Column R is what the total sum should be.
Any help would be appreciated!
4 Replies
For dynamic approach you can use BYROW() function. Check the attached file.
=BYROW(A8:A15,LAMBDA(x,SUMIFS(P8:P15,A8:A15,x)))You can also use SUMIFS() function like-
=SUMIFS($P$8:$P$15,$A$8:$A$15,A8)mathetes Thanks for the quick response! I tried the SUMIF formula actually, but it doesn't quite for me because I have to specifically mention a criteria ("MGMTCO001" in this case). The criteria in column A can change so I need a way to tell excel to get the sum anytime various values appear.
If you are on a recent Excel version that supports dynamic arrays, enter this in R8:
=SUMIF(A8:A15,A8:A15,P8:P15)Just make sure that the cells below are empty.
Otherwise, a simple SUMIF will do just fine, as mathetes suggested. Enter this in R8 and copy it down.
=SUMIF($A$8:$A$15,A8,$P$8:$P$15)Although it works just fine, there is no real need for SUMIFS and/or a LAMBDA function.
