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

Find more posts tagged with

Formulas

References

Accepted answers

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

All comments

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

Mark_Molloy

Thank you @Protonsponge . That has worked a treat.

I had been reversing my formulas and got locked in a death spiral on Friday😂 , thanks for sorting that out

Protonsponge

@Mark_Molloy It happens to us all, I got myself in a knot last week too and was saved by the community.

Really pleased you got sorted!

Protonsponge

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.