Solved: Sum a column B where column A is the same in two different sheets

[Pats83]

Hi There
I have two excel sheets Mpro and Employee.
The Employee sheet has 2 columns
MproID | Trips
15 | 2
15 | 5
35 | 9
35 | 3
52 | 1

The Mpro sheet has two columns has well
MproID | TotalTrips
15 |
35 |
52 |

As of right now the MproID in the Mpro sheet is already unique and I need to total the Trips column under the employee sheet when the MproIds are the same and then display it in the row for TotalTrips where the mproId is the same.
I am using VBA to try and create this.
Thanks!

[SamT]

Excel SUMIF Function is faster and more elegant.

If there's a need for VBA, the WorksheetFunction SumIf is still fastest.

[Pats83]

Using the SumIf function how would I compare the Employee MproID with the Mpro MproID?

I am not sure how to use the SumIf function as well.

[Pats83]

=IF(Mpro!A2=Employee!B:Employee!B,SUMIF(Employee!B:Employee!B,Employee!B=Em ployee!B,Employee!J:Employee!J),"")\

This is what I am trying and I am getting #NAME? in the fields now.
any tips would be great

[SamT]

Hi There
I have two excel sheets Mpro and Employee.
The Employee sheet has 2 columns
MproID | Trips
15 | 2
15 | 5
35 | 9
35 | 3
52 | 1

The Mpro sheet has two columns has well
MproID | TotalTrips
15 |
35 |
52 |

As of right now the MproID in the Mpro sheet is already unique and I need to total the Trips column under the employee sheet when the MproIds are the same and then display it in the row for TotalTrips where the mproId is the same.
I am using VBA to try and create this.
Thanks!

Where A is the MProID column and B is the Trips Column:
SUMIF(range,criteria,sum_range)
Formula goes in in Mpro Sheet.Range("B2") (the cell next to the "15" in your example. Copy down to bottom of list.


range is "Employee!A:A"
criteria is "A2"
sum_range is "Employee!B:B"

Formula =
[VBA]=SUMIF(Employee!A:A,A2,Employee!B:B)[/VBA]

[Pats83]

Thanks SamT!

[Pats83]

Hi not sure how to mark this as unsolved or if I should open a new thread, but I am do require this in VBA now as in an excel formula it is running extremely slow.
I tried and am having no luck I am not sure how to display the information now.
Thanks

[SamT]

Hi not sure how to mark this as unsolved or if I should open a new thread, but I am do require this in VBA now as in an excel formula it is running extremely slow.
I tried and am having no luck I am not sure how to display the information now.
Thanks

Slow?

It's going to be faster than VBA. How may rows are in the two sheets?
What other formulas are you using?

Can you attach a sample sheet? You have to "Go Advanced" to "Manage Attachments."

[SamT]

I'll bet that you just pasted my example into B2.

In B2, type "SUMIF(" without the quotes.
Click on tab "Employee"
Select the used cells in column A
Click on tab "Mpro"
Type "," (Comma, without quotes)
Click on Cell A2
Type "," again
Tab to "Employee"
Select the used range in Column B
Type ")" without quotes
Press Enter
Copy cell B2 down as far as Column A has values.

Look at the new formula in B2