HLOOKUP plus COUNTIFS?

[Minoli]

Hi everyone, I hope you can shed some light on my calculation!

I want to combine a LOOKUP with a COUNTIFS, but I am not sure which order to nest it, or if maybe there is another formula that would complete my calculation easier.

I want to find a column of data based on the header text (like HLOOKUP does), then count rows if criteria matches in the column next to it and criteria matches in the column itself (like COUNTIFS). I made an example with an explanation in the attached. Hope it makes sense.

Book1.xlsx

Any help would be great. Thanks.

[ConneXionLost]

Do you also need to find the happiness column? If not, then try:

Please Login or Register  to view this content.

Cheers,

[Minoli]

That is perfect. Thanks very much. The happiness column is static, so I don't need to 'find' it.

It works brilliantly in the example sheet. I just need to make it work in the real thing (getting #VALUE error). I have changed "Life", 1, and 4 to a cell reference, but otherwise it is identical. Any thoughts?

[Minoli]

Sorry, I got it! It was a syntax error. Many, many thanks for your help!