Home / Formulas / Using COUNTIF and COUNTA to count items in a list

Using COUNTIF and COUNTA to count items in a list

The ExcelHelpDesk Support team received the following request for help

Problem – checking for specific cell values

Please help me with a formula
I want to say that if columns B to BX = A or N/A then True, otherwise False.
I just can’t seem to find the right formuala!!!
Debbie…

Here is a sample of the file sent through that shows what was needed

Solution – Create a formula to set the Value

The formula needed to calculate the number of non-empty cells for each row and then compare that to the number of cells that had “A” or “N/A” included. If that number differed then the result needed to be FALSE, if it matched then the result was TRUE.

The formula needed was

=IF(COUNTA(B2:AX2)=(COUNTIF(B2:AX2,"A")+COUNTIF(B2:AX2,"N/A")),TRUE,FALSE)

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment

About Excel Help Desk

Check Also

INDIRECT to extract a value from another worksheet

You can see that we have the Worksheet name in Column A and the value that is needed to be extracted in Column D. The supplied worksheet included a manually entered formula to pull through the value from the other worksheet. We will now demonstrate how to create a formula using the INDIRECT function that will automatically pull through values from other worksheets without manually entering each worksheet name and cell reference.

One comment

  1. Great service, very quick response to my query, very helpful & friendly. Many thanks Debs :-)