Formula to sum data in another sheet that meets a certain criteria
Please help!
I've got two sheets:
Sheet 1= called 'Requisition' (the main sheet) & Sheet 2 = called 'Dashboard Data'
I want to put a formula in the 'Dashboard Data' sheet that adds (sums up) data that's in the 'Requisition' sheet. The information I need is a combination of two columns.
Column 1 = called 'Number to Graduate' & Column 2 = called 'Location'
I need to sum up all the numbers in column 'Number to Graduate' that is related to the selected location in column 'Location'.
Here is an example of what I need to sum up.
I need all the numbers in the 'Number to Graduate' column to be summed up that corresponds with the relevant locations (Melbourne, Ashville, Dearborn, etc.) in the 'Location' column. In this case it should show Houston = 0, Melbourne = 72, Dearborn = 27, Asheville = 16  I need those totals to reflect in the section indicated below which is in sheet 'Dashboard Data'
Thanks!
Best Answer

Hey
I looked at the screenshot above  what is the name of the column on your dashboard sheet where the Requested vs Actual Labels show up? Just that column name
Answers

Hey @Desire
You need a SUMIFS formula. It has the syntax of =SUMIFS(range you want summed, range 1, criteria 1, range 2, criteria 2, etc). You always add criteria as a range, criteria pair. You can have from 1 to indefinite pairs within the SUMIFS.
=SUMIFS({main sheet Number to Graduate column}, {main sheet Location column}, [Requested vs Actual]@row)
Because these are crosssheet references (different sheets) you must physically create the ranges through the formula window. You cannot simply copy paste this formula into your sheet.
Does this work for you?
Kelly

Hi @Kelly Moore
Thank you for your help
Unfortunately it's not working. I'm not sure about [Requested vs Actual] part? Below are the two formula's I've tried and it's not working on my side.
=SUMIFS({#2 SANDBOX  Position Requisition 2022 Range 4};{#2 SANDBOX  Position Requisition 2022 Range 2};[Requested vs Actual]@row)
=SUMIFS({#2 SANDBOX  Position Requisition 2022 Range 4};{#2 SANDBOX  Position Requisition 2022 Range 2};[Houston]@row)
I'm clearly missing something.

Hey
I looked at the screenshot above  what is the name of the column on your dashboard sheet where the Requested vs Actual Labels show up? Just that column name

Oh my goodness, it worked, I changed it to the column name, thank you so much!!!
=SUMIFS({#2 SANDBOX  Position Requisition 2022 Range 4}; {#2 SANDBOX  Position Requisition 2022 Range 2}; [Primary Column]@row)
Help Article Resources
Categories
Check out the Formula Handbook template!