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

Mark_Molloy

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

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

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