SUMIF with VLOOKUP nested

M

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
 
V

vishu

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

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top