SUMIF using 2 sheets with multiple reference columns to sum a total column

jax69

SHEET 1 has the following columns:
Name, Type, Total

SHEET 2 has the following columns:
Name, Total Used

When the SHEET 2 "Name" column matches the SHEET 1 "Name" column and SHEET 1 "Type" = "PTO" then sum SHEET 1 "Total" column and place value in SHEET 2 "Total Used" column

VBAGuru

On Sheet 2 in the Total Used column use a formula similar to below. You will need to link the ranges to be the full columns on Sheet 1.

=Sumifs({Sheet 1 Total},{Sheet 1 Type},"PTO",{Sheet 1 Name},[Sheet 2 Name]@Row)

VBAGuru

The brackets are for when a column name contains a space, so it would be like [Employee Name]@row. In this case since you just have it labeled Name, you can skip the brackets and just use Name@row.

VBAGuru

On Sheet 2 in the Total Used column use a formula similar to below. You will need to link the ranges to be the full columns on Sheet 1.

=Sumifs({Sheet 1 Total},{Sheet 1 Type},"PTO",{Sheet 1 Name},[Sheet 2 Name]@Row)

jax69

I updated my formula to this (I selected the whole column and the formula generator put in the name of the worksheet and looks like the column #)

=Sumifs({OOO Tracker Total Hours OOO},{OOO Tracker Type of Absence},"PTO",{OOO Tracker Team Member},[Name@Row])

I updated my formula now I get #UNPARSEABLE - I can't figure out what is out of place…

VBAGuru

Last part should just be Name@row. Eliminate the brackets.

VBAGuru

The brackets are for when a column name contains a space, so it would be like [Employee Name]@row. In this case since you just have it labeled Name, you can skip the brackets and just use Name@row.

jax69

AHHHHH - so simple yet powerful. That worked - thank you so much!

VBAGuru

@jax69 So glad that worked for you!