If condition with Sumifs...help

[SG]

Hi Experts,

I want to sum the values on basis of the status.I know it would get solved through if condition but still didn't got the exact match of formulas to get the results. Please help me with the solution. PFA file for reference.

 

[SG]

please use this file.

 

[Somendra Misra]

Hi SG,

DO you want say for ex.

For Neha---HD----Completed HD-----Not Completed
---SD-----Completed SD-----Not Completed

If so, why not add an extra criteria range, in your present formula covering column A of Sheet1???

Regards,

 

[SG]

Nopes....actually i want that for Neha..in type "HD", if the status is completed or not completed or both, then it would sum that amount otherwise if any other status comes up, then it wouldn't add that amount.

 

[Somendra Misra]

@SG

Try in B2 on summary sheet and copy right and down.

=SUMPRODUCT(($A2=Sheet1!$B$2:$B$28)*(Sheet1!$C$2:$C$28=Summary!B$1)*(Sheet1!$A$2:$A$28=TRANSPOSE(Summary!$F$2:$F$3))*(Sheet1!$D$2:$D$28))

Note this is an array formula so must be entered with Ctrl+Shift+Enter.

Just check the results.

Regards,

 

[SG]

yeahhhhhhhh...............It works....thnku so much....

 

[Somendra Misra]

You are Welcome!!

Regards,

 

[Haseeb A]

Hello SG,

You already have SUMIFS in your file, just add one more range Sheet1!$A:$A,$F$2:$F$3 & summed with SUMPRODUCT

=SUMPRODUCT(SUMIFS(Sheet1!$D:$D,Sheet1!$C:$C,B$1,Sheet1!$B:$B,$A2,Sheet1!$A:$A,$F$2:$F$3))

 

[Debraj]

Another approach..
Condition
Crate a condition table like below.


Now in A1 use formula as ..
=DSUM(Sheet1!A1:D28,"Amt",Summary!E1:G3)

Now select A1 to C6.
Click Data > What If Analysis > Data Table


Use Row Input Cell as G3 & Column Input cell as F3.
And you are done..

PS: Obviously.. it not better than SUMIF master Haseeb A's formula.. but just want a fusion of DATA TABLE & DSUM two rarely used feature of excel

 

[SG]

Hey thanku so much Haseeb & Debraj. I found these ways by only you guys..!!!

 

[Debraj]

Thanks for feedback..