Sumproducts or sumifs to enhance the results

[sanjuss2]

Date SB KTY KBT CD DDTY FDR
01-10-2015 25 36 20 34 37 32
30-09-2015 33 44 20 26 29 32
05-11-2015 41 52 20 18 21 32
31-12-2015 49 60 20 10 13 32
31-10-2015 57 68 20 2 5 32
05-03-2014 65 76 20 -6 -3 32

Answer How I can get these results
Date 31-12-2015
Total
69 SB KBT
105 FDR DDTY KTY
10 CD


I used Forumula
49
0
0
'=SUMPRODUCT((A3:A8=B11)*(B2:G2=B13:G13)*(B3:G8))

[shukla.ankur281190]

Try only in your formula changes

For first condition SB KBT =SUMPRODUCT((A2:A8=B11)*(B1:G1=B12)*(B2:G8))+SUMPRODUCT((A2:A8=B11)*(B1:G1=D12)*(B2:G8)) or =SUMIFS(INDEX(B2:G7,,MATCH(B12,B1:G1,0)),A2:A7,B11)+SUMIFS(INDEX(B2:G7,,MATCH(D12,B1:G1,0)),A2:A7,B11)

For Second Condition =SUMPRODUCT((A2:A8=B11)*(B1:G1=C12)*(B2:G8))+SUMPRODUCT((A2:A8=B11)*(B1:G1=F12)*(B2:G8))+SUMPRODUCT((A2:A8=B11)*(B1:G1=G12)*(B2:G8)) or =SUMIFS(INDEX(B2:G7,,MATCH(C12,B1:G1,0)),A2:A7,B11)+SUMIFS(INDEX(B2:G7,,MATCH(F12,B1:G1,0)),A2:A7,B11)+SUMIFS(INDEX(B2:G7,,MATCH(G12,B1:G1,0)),A2:A7,B11)

For Third Condition =SUMPRODUCT((A2:A8=B11)*(B1:G1=E12)*(B2:G8)) or =SUMIFS(INDEX(B2:G7,,MATCH(E12,B1:G1,0)),A2:A7,B11)

Recommended way

Assuming your data start with A1 to G7 and your first criteria which date is in B11 cell and second criteria which is {"SB","KTY","KBT","CD","DDTY","FDR"} in B12:G12

Try



B13=SUMPRODUCT(($A$2:$A$7=$B$11)*($B$1:$G$1=B$12)*($B$2:$G$7)) and drag towards the cell

make helper column naming SB+KBT in I12, KTY+DDTY+FDR in J12 & CD in K12 cell

Add the particular cells as you want.

Check the attached file,

[sanjuss2]

Thanks for reply
but this is dummy data
Your solutions won't solve the issue bcoz I have 179 header columns so as per your solution it will not permissible due to excel limitations
Please urgent help required .........


Thanks in advance

[shukla.ankur281190]

can you provide your workbook here??

[sanjuss2]

I have attached few data for your perusal.
in Book 1 Sample Sheet

Please check this

[shukla.ankur281190]

It is the same file what i have uploaded in post#2

[Khalid Taha]

Use this function
=SUMPRODUCT((A2:A7=A5)*(B2:G7)*((B1:G1="FDR")+(B1:G1="DDTY")+(B1:G1="KTY")))
change this part as requared
((B1:G1="FDR")+(B1:G1="DDTY")+(B1:G1="KTY"))

[sanjuss2]

I have used this & its worked

A11 =SUMPRODUCT(($A$2:$A$7=$B$9)*(($B$1:$G$1=B11)+($B$1:$G$1=C11)+($B$1:$G$1=D11)),$B$2:$G$7) copy down