Help with Countif with multiple criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1263788%22%20slang%3D%22en-US%22%3EHelp%20with%20Countif%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1263788%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%2C%3C%2FP%3E%3CP%3EI%20am%20working%20on%20an%20excel%20document%20and%20want%20to%20use%20the%20COUNTIFS%20option%20to%20help%20me%20get%20some%20data.%20However%20I%20keep%20getting%20an%20error%20message%20although%20I%20am%20putting%20in%20the%20right%20formula.%20Any%20help%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1263788%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1263813%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Countif%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1263813%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600562%22%20target%3D%22_blank%22%3E%40DrAntAbs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20may%20only%20confirm%20that%20COUNTIFS()%20in%20Excel%20works%20correctly%20-%20millions%20of%20people%20around%20the%20globe%20use%20it%20and%20have%20correct%20results.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20hard%20to%20say%20what's%20wrong%20in%20your%20case%20without%20concrete%20example.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264261%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Countif%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264261%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%3BMany%20thanks%20for%20getting%20back%20to%20me%20sir.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20is%20this%2C%3C%2FP%3E%3CP%3EI%20want%20to%20count%20the%20number%20of%20tests%20were%20positive%20for%20a%20particular%20test%20out%20of%20about%20400%20sample%20cohort.%20I%20therefore%20used%20COUNTIFS%20where%20I%20set%20first%20range%2Cthen%20the%20first%20criteria%20(excel%20gives%20me%20a%20value%20for%20this%20first%20part)%20but%20when%20I%20set%20the%20second%20range%20and%20second%20criteria%2C%20I%20get%20a%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20used%20the%20COUNTIFS%20function%20already%20as%20part%20of%20the%20audit%20I%20am%20doing%20so%20I%20know%20it%20should%20work%2C%20I%20just%20don't%20know%20why%20this%20one%20isn't.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264291%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Countif%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264291%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600562%22%20target%3D%22_blank%22%3E%40DrAntAbs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlthough%2C%20I%20can't%20see%20your%20data%2C%20but%20I%20believe%20you%20can%20use%20PivotTable%20to%20achieve%20the%20count%20you%20want.%20If%20possible%20to%20upload%20the%20file%20or%20a%20sample%20file%2C%20I%20can%20show%20you%20how%20to%20achieve%20your%20desire%20report%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264326%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Countif%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264326%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600562%22%20target%3D%22_blank%22%3E%40DrAntAbs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAgain%2C%20the%20question%20is%20a%20bit%20abstract.%20Perhaps%20you%20may%20provide%20some%20sample%20of%20not%20working%20properly%20function%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264336%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Countif%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264336%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%20thanks%20for%20getting%20back%20to%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20an%20example%20of%20the%20document.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20I%20want%20to%20get%20the%20total%20number%20of%20MBRAFQ%20that%20are%20GA2001.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264363%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Countif%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264363%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600562%22%20target%3D%22_blank%22%3E%40DrAntAbs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHello%2C%20I%20have%20seen%20your%20data.%20Although%2C%20the%20spreadsheet%20wasn't%20well%20designed%20because%20headings%20in%20row%204%20are%20missing.%20Kindly%20let%20me%20know%20which%20column%20you%20want%20to%20count%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abiola1_0-1585572178346.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180503i3CF0F0D3DF1AF11C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abiola1_0-1585572178346.png%22%20alt%3D%22Abiola1_0-1585572178346.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264417%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Countif%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264417%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%20The%20heading%20ALL%20PROCEDURE%20CODES%20applies%20to%20columns%20B-K%20and%20ALL%20DIAGNOSIS%20CODES%20applies%20to%20columns%20M-V.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20want%20to%20count%20all%20%3CSTRONG%3EMBRAFQ%3C%2FSTRONG%3E%20in%20B-K%20that%20are%20%3CSTRONG%3EGA201%3C%2FSTRONG%3E%20IN%20M-V.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20makes%20sense%20please%20sir.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264424%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Countif%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264424%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600562%22%20target%3D%22_blank%22%3E%40DrAntAbs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBelow%20is%20a%20classical%20example%20of%20how%20to%20design%20spreadsheet.%20All%20the%20headings%20should%20be%20visible%20and%20there%20shouldnt%20be%20empty%20cells%2Fcolumns%2Frows%20without%20data.%20This%20makes%20it%20easily%20to%20use%20myriads%20of%20Excel%20functionalities%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abiola1_0-1585572872569.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180511i35FE56C6A068C99D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abiola1_0-1585572872569.png%22%20alt%3D%22Abiola1_0-1585572872569.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264455%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Countif%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264455%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600562%22%20target%3D%22_blank%22%3E%40DrAntAbs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20are%2064%20MEGFRQ%20between%20column%20B%20and%20J%3C%2FP%3E%0A%3CTABLE%20width%3D%2279px%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2279px%22%3EMEGFRQ%26nbsp%3B%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3Ekindly%20see%20the%20attached%20file%20also%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abiola1_1-1585573445188.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180513iDA39660E5AFB398B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abiola1_1-1585573445188.png%22%20alt%3D%22Abiola1_1-1585573445188.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1265206%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Countif%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1265206%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%20Many%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20have%20already%20used%20the%20COUNTIF%20function%20to%20determine%20the%20total%20number%20of%20each%20test%20code%2C%20e.g.%20MBRAFQ%2C%20MCFEGFR%2C%20MHER%2C%20HER2%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%20help%20with%20is%26nbsp%3B%3CSPAN%3Ecounting%20all%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EMBRAFQ%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%26nbsp%3Bthat%20are%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EGA201%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EAll%20MCFEGR%20that%20are%20GA201%3C%2FP%3E%3CP%3EAll%20MHER%20that%20are%20GA201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKindly%20see%20attached%20showing%20what%20I%20mean.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20have%20used%20COUNTIFS%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Good morning,

I am working on an excel document and want to use the COUNTIFS option to help me get some data. However I keep getting an error message although I am putting in the right formula. Any help appreciated. 

10 Replies
Highlighted

@DrAntAbs 

I may only confirm that COUNTIFS() in Excel works correctly - millions of people around the globe use it and have correct results.

 

It's hard to say what's wrong in your case without concrete example.

Highlighted

@Sergei Baklan Many thanks for getting back to me sir.

 

The issue is this,

I want to count the number of tests were positive for a particular test out of about 400 sample cohort. I therefore used COUNTIFS where I set first range,then the first criteria (excel gives me a value for this first part) but when I set the second range and second criteria, I get a 0.

 

I have used the COUNTIFS function already as part of the audit I am doing so I know it should work, I just don't know why this one isn't. 

Highlighted

@DrAntAbs 

 

Hello, 

Although, I can't see your data, but I believe you can use PivotTable to achieve the count you want. If possible to upload the file or a sample file, I can show you how to achieve your desire report

Highlighted

@DrAntAbs 

Again, the question is a bit abstract. Perhaps you may provide some sample of not working properly function? 

Highlighted

@Abiola1  thanks for getting back to me.

 

I've attached an example of the document.

 

For example I want to get the total number of MBRAFQ that are GA2001.

Highlighted

@DrAntAbs 

 

Hello, I have seen your data. Although, the spreadsheet wasn't well designed because headings in row 4 are missing. Kindly let me know which column you want to count

 

Abiola1_0-1585572178346.png

 

Highlighted

@Abiola1  The heading ALL PROCEDURE CODES applies to columns B-K and ALL DIAGNOSIS CODES applies to columns M-V. 

 

So I want to count all MBRAFQ in B-K that are GA201 IN M-V.

 

I hope that makes sense please sir.

 

Thanks 

Highlighted

@DrAntAbs 

Below is a classical example of how to design spreadsheet. All the headings should be visible and there shouldnt be empty cells/columns/rows without data. This makes it easily to use myriads of Excel functionalities 

 

Abiola1_0-1585572872569.png

 

Highlighted

@DrAntAbs 

 

There are 64 MEGFRQ between column B and J

MEGFRQ 

kindly see the attached file also
Abiola1_1-1585573445188.png

 

 

Highlighted

@Abiola1  Many thanks.

 

However, I have already used the COUNTIF function to determine the total number of each test code, e.g. MBRAFQ, MCFEGFR, MHER, HER2 etc.

 

What I need help with is counting all MBRAFQ  that are GA201

All MCFEGR that are GA201

All MHER that are GA201.

 

Kindly see attached showing what I mean.

 

Thanks 

When I have used COUNTIFS