I'm brand new to using Excel and I am trying to summarize an inventory from a rather large source sheet. On the summary sheet, I used =UNIQUE(INVENTORY!B8:B47) to tell me all of the things we carry, and =SUMIF(INVENTORY!B8:B47, SUMMARY!B8#, INVENTORY!D8:D47) to display how many we have in stock. It's important to know how many we have of each color so I'm trying to find a way to list that. Preferrably the output of this function would just be a list of the colors we have and how many of that color we have in stock for each unique item we have. Everything we have comes in different colors so I'd hate to list them all and use the count function in the basic way that I know how. I've tried a few things but, obviously, none of them work!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Help summarizing inventory spreadsheet
- Thread starter girkinz
- Start date
Skybluekid
Well-known Member
- Joined
- Apr 17, 2012
- Messages
- 1,231
- Office Version
- 365
- Platform
- Windows
Welcome to the Board
Are you able to show a sample of data and expected results?
If you are simply counting, try using Countif, instead of sumif?
Are you able to show a sample of data and expected results?
If you are simply counting, try using Countif, instead of sumif?
Upvote
0
Thanks for responding! This is kind of a simple example because it doesn't show how many color categories I actually have to account for. I've tried countif in the ways I know how, but I'm not sure how to avoid listing every color we have when not every color is applicable to every item. If there was something that could just come back with each applicable color and how many times it appears for each item that would be ideal!
Attachments
Upvote
0
Skybluekid
Well-known Member
- Joined
- Apr 17, 2012
- Messages
- 1,231
- Office Version
- 365
- Platform
- Windows
Having looked at the data you posted, it does look like the formula you are using works
| Book1 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Item | Color | Stocked | Total stock | |||||
| 2 | 331 | Blue | 5 | 331 | 5 | ||||
| 3 | 332 | Blue | 2 | 332 | 4 | ||||
| 4 | 332 | Red | 2 | 337 | 3 | ||||
| 5 | 337 | Orange | 3 | 311 | 7 | ||||
| 6 | 311 | Orange | 1 | 312 | 12 | ||||
| 7 | 311 | Blue | 1 | 314 | 6 | ||||
| 8 | 311 | Blue | 1 | ||||||
| 9 | 311 | Red | 4 | ||||||
| 10 | 312 | Red | 3 | ||||||
| 11 | 312 | Blue | 3 | ||||||
| 12 | 312 | Red | 6 | ||||||
| 13 | 314 | Blue | 4 | ||||||
| 14 | 314 | Red | 2 | ||||||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2:E7 | E2 | =UNIQUE(A2:A14) |
| F2:F7 | F2 | =SUMIF(A2:A14,E2#,C2:C14) |
| Dynamic array formulas. | ||
Upvote
0
Similar threads
- Question
- Question
