Number of instances in column depending on other columns

[mattyn]

Hi again all

Hopefully I can explain this properly.

Worksheet is a large manpower spreadsheet.

Column D (starting at Cell D9 is text of job titles drawn from a drop down list - where the data is held on worksheet Data.
Column M (starting at Cell M9) can be either "Yes" or blank.

In another cell, I need to count how many times Yes appears in Col M when the corresponding cell on Col D has a selected few job titles. I found this online, but it returns TRUE. I want it to count how many "yes" are in Col M.

=OR(AND('Unit Manning'!D9=Data!$C$1,'Unit Manning'!D9=Data!$C$2,'Unit Manning'!D9=Data!$C$8),('Unit Manning'!T9="Yes")

Does this make sense? I cannot use programming (restrictions on work IT system) hence the AND/OR statement above.

Many thanks.

 

[alansidman]

TRy using Countifs function. Here is a link

https://www.techonthenet.com/excel/formulas/countifs.php

 

[mattyn]

TRy using Countifs function. Here is a link

https://www.techonthenet.com/excel/formulas/countifs.php

Thank you. This is not so easy - to start simply in the cell i want the answer I tried this:

=COUNTIFS('Unit Manning'!D9:D500,"=Data!C1",'Unit Manning'!T9:T500,"=Yes")

And it gives the answer 0 - when definitely both ranges have the of the criteria - therefoe the answer should be 1.

What am I missing?

 

[mattyn]

Thank you. This is not so easy - to start simply in the cell i want the answer I tried this:

=COUNTIFS('Unit Manning'!D9:D500,"=Data!C1",'Unit Manning'!T9:T500,"=Yes")

And it gives the answer 0 - when definitely both ranges have the of the criteria - therefoe the answer should be 1.

What am I missing?

Well I tried it without cell referencing - and used text - it seems to work then.

 

[Peter_SSs]

=COUNTIFS('Unit Manning'!D9:D500,"=Data!C1",'Unit Manning'!T9:T500,"=Yes")

Try omitting everything red.