Sum if multiple columns meet criteria

[Mychaltb]

Hi everyone,

I am trying to come up with a formula that will sum the total amount if more than one column meets the required criteria. Please see sample data below to solve for ?

Sheet 1
() = column
Category (A) VP(B)
A ?
B ?
C ?
D ?
Total =Sum(B2:B5)


Sheet 2
Category (A) Title(B) Amount (C)
A VP 5
A VP 7
C Director 1
D Director 5
E VP 8
B VP 2
D VP 3
B VP 9
C Director 4
A Director 6

What formula would sum the total amount of all the VPs that are category A and so on? I will eventually have to do this for the other positions such as Director, Manager, etc.

Thank you in advance!

 

[Gerald Higgins]

I don't understand the relationship between sheet 1 and sheet 2.

But ignoring sheet 1 for a minute, this should sum "All VPs in category A", assuming the answer should be 12, i.e. 5+7.

=sumifs(c2:c100,a2:a100,"A",b2:b100,"VP")

Amend the range references to suit.

An alternative solution could be a pivot table.

 

[Scott Huish]

=sumifs(c:c,a:a,"a",b:b,"vp")

 

[Tetra201]

Try putting this formula in cell B2 on Sheet 1, then copying down as needed:

=SUMIFS('Sheet 2'!$C$2:$C$11,'Sheet 2'!$B$2:$B$11,Sheet1!B$1,'Sheet 2'!$A$2:$A$11,Sheet1!$A2)