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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies