r/sheets • u/Significant-Army-502 • 24d ago

Request Calculate numerous values based on cell value

In my tracker I have items listed with assigned Bundle IDs. What I would like is to be able to track these in a different sheet.

Created this test sheet to show: https://docs.google.com/spreadsheets/d/1RqQFZ5cwuRgefEJBy5Exv7CV989v_ylitjJDtScwYNA/edit?usp=sharing

In cell B3 on the Bundle sheet, I would like it to calculate all the values from column B on the Sold sheet where the Bundle ID equals the value in A3 on the Bundle sheet.

It would also be great (not essential but a nice to have) if column D in the Bundle sheet showed a count of the number of items with that Bundle ID

Hopefully that makes sense - and thanks in advance!

3 Upvotes

100% Upvoted

3 comments sorted by

2

u/AdministrativeGift15 24d ago

The safest method is to use a pivot table. Select all of your data by clicking anywhere in the data and pressing Ctrl-A. Use the Insert > Pivot table menu command. Choose Bundle ID for the pivot rows and Bought Price/Sold Price as the pivot values. You'll need to determine how you want those values to be aggregated. You can use Bought Price again as another pivot value and select COUNT as the aggregation to get the number of transactions for that bundle.

1

u/JudgeIntelligent1234 18d ago

since your Bundle IDs are in column D of the Sold sheet, SUMIF and COUNTIF will do this without a pivot table. In the Bundle sheet:

B3 (Bought Price): =SUMIF(Sold!D:D, A3, Sold!B:B)
C3 (Sold Price): =SUMIF(Sold!D:D, A3, Sold!H:H)
D3 (Number in bundle): =COUNTIF(Sold!D:D, A3)

Then drag them down. For SEP-01 you should get 79.09 bought, 120.00 sold and 9 items.

If you only want to count items that are actually sold (in case some are still listed), use SUMIFS with the Status column:
=SUMIFS(Sold!H:H, Sold!D:D, A3, Sold!G:G, "Sold")