| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| DSUM with variable Criteria | Excel Worksheet Functions | |||
| sumproduct with one variable criteria? | Excel Worksheet Functions | |||
| SUMPRODUCT WITH A VARIABLE CRITERIA? | Excel Worksheet Functions | |||
| Variable Criteria? | Excel Worksheet Functions | |||
| countif variable criteria | Excel Discussion (Misc queries) | |||
Hybrid Mode
