COUNTIF AND Issues - Counting across columns

[DSL1970]

All,

I am having issues with a table that will have other data added daily. I have three columns where I have Chain of Command 1, Chain of Command 2 and FT/PT.

Based on a pre-selected leader, I can get the count of how many times that leader chows up in the list using COUNTIF; however, I am having a problem in getting the number of Full-Time (FT) employee count based on this pre-selected leader. The number can only be equal to or less than the count number.Any assistance to get the number of FT workers by leader would be appreciated.

Which Leader?	Count
LEADER 1	=COUNTIF(A5:B24,A2)	=COUNTIF(AND((A5:B24,A2), C5:C24="FT"),COUNTIF( C5:C24="FT"),"")
CHAIN OF COMMAND 1	CHAIN OF COMMAND 2	PT/FT
LEADER 1	LEADER 19	FT
LEADER 1	LEADER 20	FT
LEADER 1	LEADER 21	PT
LEADER 2	LEADER 22	FT
LEADER 3	LEADER 23	FT
LEADER 4	LEADER 24	FT
LEADER 5	LEADER 25	FT
LEADER 6	LEADER 26	FT
LEADER 7	LEADER 27	FT
LEADER 8	LEADER 28	FT
LEADER 9	LEADER 29	FT
LEADER 10	LEADER 30	FT
LEADER 11	LEADER 31	FT
LEADER 12	LEADER 32	FT
LEADER 13	LEADER 33	FT
LEADER 14	LEADER 34	FT
LEADER 15	LEADER 35	FT
LEADER 16	LEADER 36	FT
LEADER 17	LEADER 37	FT
LEADER 18	LEADER 38	FT

<tbody>
</tbody>

 

[TheSpiderman]

Hello DSL1970,

I think this will solve your problem :

Uses COUNTIFS instead of COUNTIF

=COUNTIFS(A5:A24,A2;C5:C24,"FT")+COUNTIFS(B5:B24,A2;C5:C24,"FT")

Have a nice day!
Den

 

[jtakw]

Hi,

Also posted here: https://www.mrexcel.com/forum/excel...issues-counting-across-columns.html?highlight=

 

[TheSpiderman]

Sorry jtakw, but the solution you pointed out from Momentman is not working, because if you have your leader in column "CHAIN OF COMMAND 2" it will not count it
For example : "LEADER 1" in cell "B12" will still write 2 instead of 3

 

[jtakw]

Sorry jtakw, but the solution you pointed out from Momentman is not working, because if you have your leader in column "CHAIN OF COMMAND 2" it will not count it
For example : "LEADER 1" in cell "B12" will still write 2 instead of 3

I'm not pointing out a Solution, I'm pointing out a Double Post, so people could refer to it and vice versa, so nobody wastes their time incase it's solved.
In this case, maybe people reading the Other post don't know about your suggested solution here, and they're spending time coming up with something there.

We should always point out Double Posts for this reason.

Edit: and MODs usually lock and/or delete double/re-posts.

 

[DSL1970]

Thank you All. I got it solved with your help!