SUMIF with VLOOKUP nested

[murkaboris]

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

 

[Herbert Seidenberg]

Excel 2007 PivotTable, PivotChart
No formulas of any kind needed.
Translation to 2003 will nuke it.
http://www.mediafire.com/file/zyximomzno4/04_08_09a.xlsx

 

[vishu]

Hi Monika,
Use SUMPRODUCT formula
=SUMPRODUCT(--($B$2:$B$30="CT"),--($D$2:$D$30="09-Q1"),($C$2:$C$30))

 

[murkaboris]

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

 

[murkaboris]

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