Counting quantities from another sheet that correspond with a reference on original sheet

Hi folks

Really need siome guidance. I've tried the AI tool, but not getting anywhere. We are carrying out a network audit at multiple sites.

I need to be able to display the SUM from another sheet when it meets the critera from current sheet: For example, I want to bable to display a summary of additiopnal switches needed when it matches the site code of the original Sheet

We will have multiple line entries that will have the same Site Code and I wish to summarise them in a Master Sheet

Main sheet is CES Recommendations / Remediation Master

The Lookup sheet is 1. CES Switch Audit

For Example, I've created a formula that will determine if the site needs an addtional switch based on the number of free ports available </= 4. I want to be able to summarise that Site E3001 needs an additional switch on the

CES Recommendations / Remediation Master

TIA

Answers

  • Protonsponge
    Protonsponge ✭✭✭✭✭✭

    Hello @Mark_Molloy,

    If are looking to sum the number of [Add Switch] based upon [Site Code], I think SUMIF could be useful to you.

    I made a quick demo below using the cross sheet formula below. This will sum your [Add Switch] Column when the [Site Code] in your data sheet is the same as [Site Code]@row in your look up sheet.

    =SUMIF({2. CES Switch Audit_Site Code}, [Site Code]@row, {2. CES Switch Audit_Add Switch})

    Data Sheet "1. CES Switch Audit"

    Look Up Sheet - "9. CES Recommendations / Remediation Master"

    All seems to be working in the demo above, I hope that is helpful to you in some way,

    Protonsponge

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!