Sumifs Formula

[sdsurzh]

Hi,


I have a source sheet and I want the result in the below file. I have explained what I want. Excel hero please suggest a formula and give an idea to overcome this issue.


http://www.2shared.com/file/U5J2eaGa/Book1.html


Thanks,

Suresh Kumar S

 

[Hui]

Suresh


In H12:

=SUMPRODUCT(($A$3:$A$7=A12)*($B$3:$B$7=B12)*($C$3:$C$7=C12)*($D$3:$D$7=D12)*(($E$3:$E$7=F12)+($E$3:$E$7=G12)),$F$3:$F$7)

Copy down

 

[Debraj]

Hi Suresh,


I would like to go with HUI, if any of the field are not blank..


but if any of the field is always blank.. and you want the formula in SUMIFS.. then you can try below also.

=SUMIFS($F$3:$F$7,$A$3:$A$7,A12,$B$3:$B$7,B12,$C$3:$C$7,C12,$D$3:$D$7,TRIM(D12&E12),$E$3:$E$7,TRIM(F12&G12))


and by the way.. little modification is HUI's formula..

=SUMPRODUCT(($A$3:$A$7=A12)*($B$3:$B$7=B12)*($C$3:$C$7=C12)*(($D$3:$D$7=D12)+($D$3:$D$7=E12))*(($E$3:$E$7=F12)+($E$3:$E$7=G12)),$F$3:$F$7)

Regards,

Deb..

 

[sdsurzh]

Thanks Hui & Deb