Help with Countif with multiple criteria

Copper 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

@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.

@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. 

@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

@DrAntAbs 

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

@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.

@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

 

@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 

@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

 

@DrAntAbs 

 

There are 64 MEGFRQ between column B and J

MEGFRQ 

kindly see the attached file also
Abiola1_1-1585573445188.png

 

 

@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