Summing a list into a chart

[Phaedran]

I apologize if this has been asked before, but I've searched for it every way I can think of & even though I'm typically pretty good figuring these formulas out, this one has me stumped. I think I'm approaching it the wrong way.

I'm selling 'widgets'. Widgets come in different colors & different materials. As I make each one I add it to a list:

A B C
1 Blue Metal
2 Blue Plastic
3 Green Wood

etc.

column D is the status, S=Sold, nothing means available for sale. (with other letters, but those are the two relevant ones)

I'm trying to set up a chart off to the side that keeps track of the number of each available & sold, eg

Blue Plastic 12 3
Green Plastic 4 7
Purple Wood 3 8

I've tried a few different ways, but as soon as I work out one issue, my formula isn't accounting for something else.

Does anyone have any thoughts or ideas I might try?

[MarvinP]

Hi Phaedran and welcome to the forum,

This looks like a pretty easy Pivot Table/Chart answer to me. If you want more help then you need to supply a sample workbook. You can attach a sample using the "Go Advanced" message area and then click on the Paper Clip Icon.

[Phaedran]

Thank you Marvin.

I was having trouble uploading a sample (the actual has personal info in it) but I've got it figured out now.

This is what I'm trying to accomplish (if I uploaded it correctly). Each time I make a new widget, I add it to the bottom of the list. I'd like the appropriate 'Sold/Available' box in H3:N11 to change appropriately. If I sell one & add 'S' in the E column, I'd like it to also update correctly. I understand that it won't be a simple fill right/down thing, but the formula is what I'm having trouble with. I've been trying variations of If(vlookup) & sum(if(and) type formulas, but I seem to be missing something. If I could get just 1 to work correctly, I shouldn't have a problem adapting it to the rest.

[Phaedran]

widgets.xlsx

Sigh... it's been one of those days.

[Phaedran]

For anyone interested, or if this pops up in someone's search & they're having the same problem I found my solution. A SUMPRODUCT array, a function I'd never heard of before.

widgets2.xlsx

[MarvinP]

Hi Phaedran,

Find the attached where I've added a Dynamic Named Range (DNR) of your data so you can make a lot more rows in your table and the data will adjust to the size of your data. I've also added an Event to trigger the Pivot Table refresh whenever you add or change anything in your table. I have also added a Splicer so you can slice and dice your data in the pivot table to filter exactly what you want to see. See if this helps with your problem instead of needing SumProduct. See if this is any better for you.

You will need to look behind the Worksheet at the VBA code to see the Event code and also at the Formula's Tab, Named Range to see the DNR.

[Phaedran]

Marvin,

I appreciate the thought & effort, but a pivot chart is not something I want for this spreadsheet. The SumProduct is doing exactly what I need it to do. The sample I attached was only for the general idea to figure out a formula-based solution. The actual chart I've applied it to is 64 rows (which is a set amount) and 14 columns (which may grow in pairs, but less than double). I also set up the SumProduct formula to check against the column/row titles instead of typing each option in quotes, so adding columns is a simple matter of copy/paste.

I understand this may not work for everyone, but I found similar issues while researching my own, so I thought I'd at least provide my solution just in case someone stumbles upon it & has their own 'Aha!' moment.

Personally, I don't like the graphic representations as I see/think in numbers. A benefit & curse of having AS/being on the spectrum, as they say. The pivot chart idea may be a good one, but not for this application. I don't need to rearrange the data list, I don't need graphs or graphics, just raw data that I can interpret very quickly, which my basic table/chart (I'm not sure the proper term in Excel-speak) is absolutely perfect for.

Again, thank you for your thoughts & efforts. It's very kind of you to use your time on my problem.