Excel 2007 : Totals based on different variables/columns.

[spinchick]

I'm trying to figure out how to add totals with 3 variables

So it's similar to this.

Column A has a list of colors (pink, blue, purple)
Column B has a number (2, 3, etc)
Column C has a type of ribbon (satin, matte, etc)

So depending on the answer the totals will change.

Someone might get 2 pink matte ribbons, and someone else might get 1 pink satin ribbon.

I need all the totals for each possible option.
Please tell me there is some way to do this. I've been fighting with this for a week now.

[DonkeyOte]

Have you considered using a Pivot Table ?

You can create a basic Pivot Table (Insert -> Pivot Table) using Col A as Row Label and Column C as Column Label and Column B as Values field
The above will generate a Matrix showing the output per combination of colours/ribbon types listed.
(note we are assuming a relatively limited number of ribbon types - ie couple of hundred max.)

If you want to use Formulae then see SUMIFS function in XL Help - this is not backwards compatible pre XL2007 but is more efficient than pre XL2007 alternatives like SUMPRODUCT etc...

If you need further assistance I would suggest posting a sample and expected / desired results.

[spinchick]

Ok so I'll try and copy from the spreadsheet (I dont see a good way to attach it)

But here is the general idea. You have a bunch of different options, and you need totals for each. One line may have 1 entry, the next may have 6.

Columns are: Color, Skeins, Fiber, Trim
Options are Purple, Pink, Blue, Hot Pink, Teal, Black.


Color Skeins Fiber Trim Color Skeins Fiber Trim Color Skeins Fiber
Purple 2 Satin 1 Purple 2 Matte 1 - - -
Pink 2 Matte 1 Pink 1 Velvet 0 Purple 2 Matte
Blue 2 Velvet Teal 2 Satin 0 Pink 2 Velvet
Blue 3 Satin 1 - - - 0 - - -
Pink 2 Satin 1 Purple 3 Matte 1 Teal 3 Satin
Hot Pink 1 Satin 1 Pink 1 Satin 1 Pink 1 Satin
Teal 3 Satin 1 Purple 3 Satin 1 - - -
Hot Pink 3 Matte 1 - - - 0 - - -
Pink 3 Matte 0 Blue 3 Matte 0 - - -
Purple 4 Velvet 0 Teal 2 Satin 1 - - -
Pink 3 Velvet 0 - - - 0 - - -
Hot Pink 1 Satin 1 Pink 1 Matte 1 Teal 1 Velvet
Black 1 Matte 0 Blue 2 Velvet 0 - - -

(Below here is what I am trying to accomplish with the above data)
Purple Satin 5
Pink Matte 6
Blue Velvet 4
Black Matte 1

Like I wouldnt care if it had Black Satin cause there is none, but I need to know 1 black Matte.

I'm doing this all by hand right now, but it's killing me (and a waste of time I'm sure)

[spinchick]

BTW I tried a pivot table, but uhm.. I couldnt get it to show up correctly.
I also tried this:
=COUNTIFS(V2:V31,R2:R31,N2:N31,J2:J31,F2:F31,"*Hot Pink*",H2:H31,L2:L31,P2:P31,T2:T31,X2:X31,"*Satin*")

But that didnt work either (it gave me 0 which i KNOW is incorrect)

[DonkeyOte]

Ok so I'll try and copy from the spreadsheet (I dont see a good way to attach it)

To attach a file click the paperclip icon in the reply window - if you can not see it click GoAdvanced.

[spinchick]

OK here is my sample