I am currently making a data report for analytical study.

For context: It is a register of people's mental health issues. There are three columns of reasons for appointment. I want to make a table that counts the amount of people who have booked in for appointments based on criteria such as their "housing status".

I have managed to work for most of my sheets.

However, there is one report that looks at multiple column criteria that some of which are blank or different values in different columns. Which seems to return #VALUE instead of a count.

For example:

This is the formula I have so far, which worked when it was single column counting with multiple criteria, but now that it is checking multiple columns it returns #value.

=COUNTIFS(HousingSituation!$F:$F,A3,Register!P:R,B3)

I thought to check each column as an individual criterion but then it only counts if all three items are same.

Here is an example of the P:R range in my main register I have had to make this up for the example and data protection:

For this the resulting value I am looking in my count table would be:

As it is one person with each of those issues.

Could anyone help me?

CodePudding user response：

Demoing the answer in the comments, where this works as long as you don't have the same reason repeated on the same row. If you do have the same reasons repeated on the same row, then a different solution is needed and I'll remove this answer.

Cell J2 formula:

=COUNTIFS(O:O,$H$2,P:P,I2)   COUNTIFS(O:O,$H$2,Q:Q,I2)   COUNTIFS(O:O,$H$2,R:R,I2)