SOLVED
Home

Using countif across two sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-389352%22%20slang%3D%22en-US%22%3EUsing%20countif%20across%20two%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389352%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20range%20of%20pollution%20levels%20that%20I%20want%20to%20compare%20to%20air%20quality%20standards.%20Data%20for%20the%20pollution%20level%20is%20one%20spreadsheet%2C%20the%20standards%20for%20it%20to%20be%20compared%20to%20are%20in%20another.%20I%20am%20using%20the%20following%20command%3A%26nbsp%3B%3DCOUNTIF(F6%3AF153%2C%22%26gt%3B%3D'%5BTox%20standards.xlsx%5DAEGLs'!%24D%2424'%22)%20but%20it%20is%20returning%200%20when%20it%20should%20report%20all%20the%20data%20as%20exceeding%20the%20standard.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20am%20I%20doing%20wrong%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimon.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-389352%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389420%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20countif%20across%20two%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389420%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F308967%22%20target%3D%22_blank%22%3E%40SimonGNCL%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389403%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20countif%20across%20two%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389403%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B-%20that%20is%20great%2C%20thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389372%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20countif%20across%20two%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389372%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F308967%22%20target%3D%22_blank%22%3E%40SimonGNCL%3C%2FA%3E%26nbsp%3B%2C%20that's%20like%3C%2FP%3E%0A%3CPRE%3E%3DCOUNTIF(F6%3AF153%2C%22%26gt%3B%3D%22%20%26amp%3B%20'%5BTox%20standards.xlsx%5DAEGLs'!%24D%2424)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
SimonGNCL
New Contributor

Hello,

 

I have a range of pollution levels that I want to compare to air quality standards. Data for the pollution level is one spreadsheet, the standards for it to be compared to are in another. I am using the following command: =COUNTIF(F6:F153,">='[Tox standards.xlsx]AEGLs'!$D$24'") but it is returning 0 when it should report all the data as exceeding the standard. 

 

What am I doing wrong please?

 

Thanks.

 

Simon.

3 Replies
Solution

@SimonGNCL , that's like

=COUNTIF(F6:F153,">=" & '[Tox standards.xlsx]AEGLs'!$D$24)

 

@Sergei Baklan - that is great, thank you!

@SimonGNCL , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies