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'


Best Answer


  • Kelly Moore
    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?


  • Desire
    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓


    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
    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!