how to sum columns by variable criteria

[Jason]

this may be easy, but it has me stumped. I need to create a spreadsheet that
will provide totals based on criteria set in a separate list.

TICKER SYMBOL MARKET VALUE SECURITY CLASSIFICATION CODE
DD 7934.4 401
GE 26974.4 416
DSX 15425 418
VZ 9506.79 429
WIN 5593.5 429
CMCSA 2601 435

I need to total the market value based on a list kept in a separate sheet

LG VAL LG BLEND LG GRTH MID VAL
400 500 600 800
402 501 630 810
418 502 631 811
484 505 635 812


I would like to build it this way because the security class codes (criteria
for summing) will change over time. I want to be able to update the list
without having to update all of the formulas.

is this possible?

 

[jlclyde]

this may be easy, but it has me stumped.  I need to create a spreadsheetthat
will provide totals based on criteria set in a separate list.

TICKER SYMBOL    MARKET VALUE   SECURITY CLASSIFICATION CODE
DD                       7934.4                    401
GE                       26974.4                          416
DSX                     15425                      418
VZ                      9506.79                    429
WIN                     5593.5                    429
CMCSA                   2601                      435

I need to total the market value based on a list kept in a separate sheet

LG VAL  LG BLEND        LG GRTH MID VAL
400     500     600     800
402     501     630     810
418     502     631     811
484     505     635     812

I would like to build it this way because the security class codes (criteria
for summing) will change over time.  I want to be able to update the list
without having to update all of the formulas.

is this possible?

Use SUMPRODUCT(( Excel Array = X) * (Excel Array = Y) * (Excel array
that you want summed)
All arrays must be the same size. Substitute your own arrays into
this formula. X and Y are the conditions that you want it to meet.
These can be variables that are placed in cells and are changed at any
time.

Jay

 

[FSt1]

hi
you could use the sumif function because the sumif function will take a cell
value as criteria.
=sumif(A1:A50,Sheet2!A1,B1,B50)
but i would be carefull about how i set it up so that i would know which
criteria the formula was returning

Regards
FSt1

 

[Dave Peterson]

Just a typo warning:

=sumif(A1:A50,Sheet2!A1,B1:B50)

(I changed b1,b50 to b1:b50)

 

[FSt1]

thanks for catching that, dave. my goof.

Regards
FSt1

 

[Jason]

this seems to be where I'm having the problem.

the number of rows in the security classification table will probably vary,
so in the formula I've been trying to point to the entire column (ex:
sheet2!c:c). It's not working. Is this my problem?

 

[Dave Peterson]

What was the formula that you tried?

=sumproduct() can't use the whole column until xl2007.

 

[Jason]

I initially tried it as a sumif, sumproduct is very confusing to me. It
makes sense "in theory" but as soon as I start, I get lost. I didn't think I
was this dumb, but apparently I am...

 

[Dave Peterson]

Does that mean you got it working?

I initially tried it as a sumif, sumproduct is very confusing to me. It
makes sense "in theory" but as soon as I start, I get lost. I didn't think I
was this dumb, but apparently I am...