Sum Mulitple Column based on the multiple Conditions using sumifs function

Hi,

I am new to this forum.

Can somebody help me to solve the following problem.

I have the following Data

A B C D E F G H I J K
1 JOB NO TOTAL AMT BOQ# QTY AMT BOQ# QTY AMT BOQ# QTY AMT

2 ABIR-0125 11000 10 2 2000 20 5 1000 30 10 8000

3 ABIR-0120 10000 30 2 2000 20 5 1000 10 10 7000

4 ABIR-0125 1000 20 2 200 10 5 800


Now from above data I want total qty and total amount for Job# ABIR-0125 & BOQ#10 which should be
Qty-->>> 7
Amt -->>> 2800

I have tried Sumifs function but it gives me total for one set of data(BOQ#, Qty, Amt) but i have multiple set of data. For getting the correct answer I have to add sumifs for each set of column which will become very long

Please if somebody can give me the simple function to add . I am using excel 2007

I am enclosing the sample file for reference

Thanks & Regards,

Kamal

Dear Dilip,

Data which I am considering for result is A1:K4

Regards,

Kamal

Thanks for the reply with a solution but I don't want to repeat sumifs function as I have so many columns.

Regards,

Kamal

Dear Dilip,


10 is not the output it is part of the condition.

Output which I need is 7 for qty column and 2800 for amt column

7 comes through addition of cell D2 AND G4 in which both condition apply i.e. BOQ = 10 and Job# ABIR-0125
and in the same way

2800 through addition of cell E2 and H4

Regards,

Kamal

Thanks a lot. It works fine for me.

Regards,

Kamal

You're Welcome!