Referencing another sheet, add totals in a column if meeting a certain criteria in another column

Desire

Hi,

I don't know which formula to use, please help.

I've got a sheet called "Position Requisition" with "TA Number To Offer" and "Location" column. In another sheet called "Dashboard Data" I want reference back to "Position Requisition" sheet to add the numbers/totals in the "TA Number To Offer" column but it must match the location entered in the "Location" column. In other words if I have 3 rows for Houston, I want to only add the Houston numbers.

Hollie Green

I would try a =Sum(Index(Collect({TA Number To Offer},{Location},[Location]@row),1) you will have to create your references so you won't be able to just copy and paste the formula. The TA Number To Offer and the Location should reference your Position Requisition sheet and the location@row should reference your Dashboard Data Sheet.

Desire

Hi, thank you. I've tried the formula, but it's not adding all the relevant cells together. i.e. I have 4 lines, 2 lines Houston and 2 lines Melbourne, I have to add the 2 lines with Houston together and the 2 lines with Melbourne together. This formal only bring one of the values across, it doesn't add/sum them.