how to sum columns by variable criteria
 

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?

how to sum columns by variable criteria
 

On Jan 7, 10:41*am, Jason wrote:
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?

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

how to sum columns by variable criteria
 

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

"Jason" wrote:

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?

how to sum columns by variable criteria
 

Just a typo warning:

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

(I changed b1,b50 to b1:b50)

FSt1 wrote:

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

"Jason" wrote:

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?


--

Dave Peterson

how to sum columns by variable criteria
 

thanks for catching that, dave. my goof.

Regards
FSt1


"Dave Peterson" wrote:

Just a typo warning:

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

(I changed b1,b50 to b1:b50)

FSt1 wrote:

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

"Jason" wrote:

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?


--

Dave Peterson

follow up question
 

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?

"Jason" wrote:

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?

follow up question
 

What was the formula that you tried?

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

Jason wrote:

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?

"Jason" wrote:

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?


--

Dave Peterson

follow up question
 

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" wrote:

What was the formula that you tried?

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

Jason wrote:

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?

"Jason" wrote:

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?


--

Dave Peterson

follow up question
 

Does that mean you got it working?

Jason wrote:

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" wrote:

What was the formula that you tried?

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

Jason wrote:

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?

"Jason" wrote:

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?


--

Dave Peterson


--

Dave Peterson