Count if where range is same but criteria is different

[vjharry]

S No Name Vehicle Having Date of Joining Experience
1 Kane 2 18-Nov-02 12 Years 10 Months
2 Morris 3 18-Nov-02 12 Years 10 Months
3 Bill 1 3-Feb-03 12 Years 08 Months
4 James 2 17-Mar-03 12 Years 06 Months
5 Rohit 1 15-Oct-03 11 Years 11 Months
6 Tom 3 1-Jan-04 11 Years 09 Months
7 Michal 5 5-Jan-04 11 Years 09 Months
8 Daniel 2 12-Jan-04 11 Years 08 Months

How many people are there who are having 1 vehicle and 3 vehicles?
Can we get this drive by Count if function?

 

[AlanY]

S No Name Vehicle Having Date of Joining Experience
1 Kane 2 18-Nov-02 12 Years 10 Months
2 Morris 3 18-Nov-02 12 Years 10 Months
3 Bill 1 3-Feb-03 12 Years 08 Months
4 James 2 17-Mar-03 12 Years 06 Months
5 Rohit 1 15-Oct-03 11 Years 11 Months
6 Tom 3 1-Jan-04 11 Years 09 Months
7 Michal 5 5-Jan-04 11 Years 09 Months
8 Daniel 2 12-Jan-04 11 Years 08 Months

How many people are there who are having 1 vehicle and 3 vehicles?
Can we get this drive by Count if function?

for 1
=COUNTIF(C2:C9,"=1")

for 3
=COUNTIF(C2:C9,"=3")

 

[gaz_chops]

Try

=SUM(COUNTIFS(C2:C9,{1,3}))

Must be entered as an array (Ctrl, Shift & Enter)

 

[vjharry]

sorry but can we do the same in a single countif formula!!

 

[AlanY]

you mean to put a '+' inbetween the two?

 

[gaz_chops]

Or try my suggestion.

 

[vjharry]

Try

=SUM(COUNTIFS(C2:C9,{1,3}))

Must be entered as an array (Ctrl, Shift & Enter)

Great its working

 

[gaz_chops]

You're welcome, did you check your other thread? I posted a new formula for you to try.

 

[vjharry]

yes that cell reference I did corrected at my own

thanks Again