CODEWITHSUNDEEP
excelworksheet-function
user2506445
user2506445

Reputation: 129

Formula to add the sum of a range of cells with same "description" only

I will go into more detail than the question title here:

So lets say in Range L3:L10 I have a list of descriptions (i.e. Red, Blue, Green, etc.). If I enter a running list of random colors into cells, lets say, F3:F600, with the quantity of widgets in that color into the cell to its right (G3:G:600), what formula can I put in M3:M10 so that it sums ONLY the number of entries for its respective color.

I have a another range on Sheet2!A3:A10 where the initial inventory of each color is stored. Ultimately I want to be able to keep a running inventory of which colors I have and how many I have of them. And so if I got a new initial inventory because of a shipment, all I would do is have to change Sheet2!A3:A10 data and it would do the rest.

I had attempted the following formula in cell M3 and dragged down to M10 but it did not work.

=Sheet2!A$3:A$10-IF(L$3:L$10=F$3:F$600,SUM(G$3:G$600),0)

I have also tried the folllowing and dragged down through M10:

=Sheet2!A3-IF(L3=F$3:F$600,SUM(G$3:G$600),0)

Neither of those work. They both seem like they work at first because when there is nothing in the F & G columns is shows the initial inventory that is listed on Sheet2! like it is supposed to, but then once I input data into the cells that is is supposed to calculate from, nothing changes.

Upvotes: 0

Views: 1127

Answers (2)

mr.Reband
mr.Reband

Reputation: 2430

I don't see why you need the aggregate names in L3:L10 and in a separate sheet Sheet2!A3:A10, but this formula will get you the aggregate for the color in L3, and you can fill down:

=SUMIF(F$3:F$600,L3,G$3:G$600)

Upvotes: 1

Qiu
Qiu

Reputation: 5761

I believe this is what you're looking for:

=SUMIF($F$3:$F$600,$L3,$G$3:$G$600)

Upvotes: 1

Related Questions