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
Best Answer
-
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
Answers
-
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
-
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
-
@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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!