# Sum if multiple columns meet criteria

#### Mychaltb

##### New Member
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.

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.

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

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)

Replies
1
Views
297
Replies
0
Views
199
Replies
11
Views
192
Replies
1
Views
67
Replies
2
Views
273

1,196,513
Messages
6,015,642
Members
441,913
Latest member
Lhayden_69

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back