sumif with horizontal

[parkkibum]

hi All,

I have

a b c d e f g
number amnt1 amnt2 amnt3 amnt4 amnt5 amnt6
1 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00
2 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00
3 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00
4 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00
5 $5.00 $5.00 $5.00 $5.00 $5.00 $5.00
6 $6.00 $6.00 $6.00 $6.00 $6.00 $6.00
7 $7.00 $7.00 $7.00 $7.00 $7.00 $7.00

please see the attached file for detailed info.

i would need a formula to sum all values under "amnt3" in Column D.

something like =sumif(a1:g8,"e",a1:g8)

thank you in advanced!!
Book1.xlsx

[LittleFry]

One option which seems to work

=SUM(OFFSET($A$1,1,MATCH(B12,B1:G1,0),7))

with the values from B1:G1 in a data validation list in B12

[Tony Valko]

Try this...

B12 = PhysicalDamagePremium

=SUM(INDEX(B2:G8,0,MATCH(B12,B1:G1,0)))

[parkkibum]

thank you all they both work!

[Tony Valko]

You're welcome. We appreciate the feedback!

[parkkibum]

Hi again,

i'm in need of help again

this time i added a new column consists of Year.

please see the attached fileBook1.xlsx

[LittleFry]

Put 2014 in B12 and 2015 in B13

Put PhysicalDamagePremium in C12 and C13

D12 copied down to D13

=SUMIF($B$2:$B$8,B12,INDEX($C$2:$H$8,,MATCH(C12,$C$1:$H$1,0)))

[parkkibum]

hi LittleFry,

thank you for the prompt response!

but would it be possible to add PhysicalDamagePremium and CompositeLiabilityPremium?
so basically put another match and get result of

$64.00=2014 PhysicalDamagePremium and CompositeLiabilityPremium

thank you

[LittleFry]

My suggestion would be to have both in different cells and then sum

D12 copied down =SUMIF($B$2:$B$8,B12,INDEX($C$2:$H$8,,MATCH(C12,$C$1:$H$1,0)))

Then sum in D14

Please Login or Register  to view this content.

[FDibbins]

You are getting to the point where you should start thinking about a small table for this - you keep adding criteria to your request