Formula to sum data in another sheet that meets a certain criteria

Desire

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!

Kelly Moore

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

Kelly Moore

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 cross-sheet 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

Desire

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.

Kelly Moore

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

Desire

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)