| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with VLOOKUP nested
Hello:
I have an xls that has the following columns: Territory ID Modality Quarter Amount I need to create a formula that would give me a sum for the amount for each modality for a specific territory on quarterly basis. Example: Terr ID Modality Amount Qtr 399 RAD -9.0 09-Q1 399 CT -9.0 09-Q1 399 Nuclear 14.4 09-Q1 399 AW 32.0 09-Q2 399 Other 7.0 09-Q2 399 CT 649.1 09-Q3 399 Other -0.8 09-Q3 399 AW 0.0 09-Q4 399 CT 0.0 09-Q4 399 Other 7.0 09-Q1 400 MR 422.8 09-Q1 400 MR 1325.4 09-Q1 400 MR 422.8 09-Q1 400 MR -422.8 09-Q2 400 MR 368.5 09-Q2 400 MR 897.5 09-Q3 400 MR 1459.0 09-Q3 400 MR -422.8 09-Q4 400 MR 379.5 09-Q4 400 CT 425.0 09-Q1 403 Nuclear 1.9 09-Q1 403 Nuclear 14.7 09-Q1 403 Nuclear 202.5 09-Q1 403 Nuclear 10.6 09-Q2 403 Other 1.7 09-Q2 403 Nuclear 1.9 09-Q3 403 Other -1.7 09-Q3 403 Nuclear 14.7 09-Q4 403 Nuclear 213.3 09-Q4 Need to return the sum into the following format: 09-Q1 09-Q2 09-Q3 09-Q4 CT MR Mammo RAD R&F Nuc PET AW Core Total thank you. Monika |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF with VLOOKUP nested
Excel 2007 PivotTable, PivotChart
No formulas of any kind needed. Translation to 2003 will nuke it. http://www.mediafire.com/file/zyximo...04_08_09a.xlsx |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF with VLOOKUP nested
Hi Monika,
Use SUMPRODUCT formula =SUMPRODUCT(--($B$2:$B$30="CT"),--($D$2:$D$30="09-Q1"),($C$2:$C$30)) "murkaboris" wrote: Hello: I have an xls that has the following columns: Territory ID Modality Quarter Amount I need to create a formula that would give me a sum for the amount for each modality for a specific territory on quarterly basis. Example: Terr ID Modality Amount Qtr 399 RAD -9.0 09-Q1 399 CT -9.0 09-Q1 399 Nuclear 14.4 09-Q1 399 AW 32.0 09-Q2 399 Other 7.0 09-Q2 399 CT 649.1 09-Q3 399 Other -0.8 09-Q3 399 AW 0.0 09-Q4 399 CT 0.0 09-Q4 399 Other 7.0 09-Q1 400 MR 422.8 09-Q1 400 MR 1325.4 09-Q1 400 MR 422.8 09-Q1 400 MR -422.8 09-Q2 400 MR 368.5 09-Q2 400 MR 897.5 09-Q3 400 MR 1459.0 09-Q3 400 MR -422.8 09-Q4 400 MR 379.5 09-Q4 400 CT 425.0 09-Q1 403 Nuclear 1.9 09-Q1 403 Nuclear 14.7 09-Q1 403 Nuclear 202.5 09-Q1 403 Nuclear 10.6 09-Q2 403 Other 1.7 09-Q2 403 Nuclear 1.9 09-Q3 403 Other -1.7 09-Q3 403 Nuclear 14.7 09-Q4 403 Nuclear 213.3 09-Q4 Need to return the sum into the following format: 09-Q1 09-Q2 09-Q3 09-Q4 CT MR Mammo RAD R&F Nuc PET AW Core Total thank you. Monika |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF with VLOOKUP nested
Hello Herbert:
Can't use pivot table due to the final format as the raw data comes from a different workbook and needs to just plug in number under the qtr based on the criteria. I don't have Excel 2007, still on 2003... need a formula that would do it... Thank you for your assistance. Monika "Herbert Seidenberg" wrote: Excel 2007 PivotTable, PivotChart No formulas of any kind needed. Translation to 2003 will nuke it. http://www.mediafire.com/file/zyximo...04_08_09a.xlsx |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF with VLOOKUP nested
Hello Vishu:
Thank you for your help it seems to be working even if I had to add another condition for the Territory ID up front. Thanks again! Monika "vishu" wrote: Hi Monika, Use SUMPRODUCT formula =SUMPRODUCT(--($B$2:$B$30="CT"),--($D$2:$D$30="09-Q1"),($C$2:$C$30)) "murkaboris" wrote: Hello: I have an xls that has the following columns: Territory ID Modality Quarter Amount I need to create a formula that would give me a sum for the amount for each modality for a specific territory on quarterly basis. Example: Terr ID Modality Amount Qtr 399 RAD -9.0 09-Q1 399 CT -9.0 09-Q1 399 Nuclear 14.4 09-Q1 399 AW 32.0 09-Q2 399 Other 7.0 09-Q2 399 CT 649.1 09-Q3 399 Other -0.8 09-Q3 399 AW 0.0 09-Q4 399 CT 0.0 09-Q4 399 Other 7.0 09-Q1 400 MR 422.8 09-Q1 400 MR 1325.4 09-Q1 400 MR 422.8 09-Q1 400 MR -422.8 09-Q2 400 MR 368.5 09-Q2 400 MR 897.5 09-Q3 400 MR 1459.0 09-Q3 400 MR -422.8 09-Q4 400 MR 379.5 09-Q4 400 CT 425.0 09-Q1 403 Nuclear 1.9 09-Q1 403 Nuclear 14.7 09-Q1 403 Nuclear 202.5 09-Q1 403 Nuclear 10.6 09-Q2 403 Other 1.7 09-Q2 403 Nuclear 1.9 09-Q3 403 Other -1.7 09-Q3 403 Nuclear 14.7 09-Q4 403 Nuclear 213.3 09-Q4 Need to return the sum into the following format: 09-Q1 09-Q2 09-Q3 09-Q4 CT MR Mammo RAD R&F Nuc PET AW Core Total thank you. Monika |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| nested sumif | Excel Discussion (Misc queries) | |||
| SUMIF nested Formula | Excel Discussion (Misc queries) | |||
| If and nested Sumif error | Excel Worksheet Functions | |||
| Will a Nested IF/SUMIF function instead of VBA? | Excel Worksheet Functions | |||
| nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
Linear Mode
