TO COUNT A CELL WITHOUTH USING COUNTIF

[Dev_1518]

So i have an issue where i have been told to count the number of cell based on a certain criteria 

in this case i have to calculate the number of people working in sales dept i have two columns Name and Dept 

I can use =COUNTIF(RANGE,CRITERIA) to find the value but the problem is i am not allowed to use COUNTIF() and have been told to use IF function to do so . Anyone out there who can help me

[#2]

@Dev_1518 

=SUMPRODUCT(N($B$2:$B$6=D2))

You can try SUMPRODUCT.

 

[#3]

@OliverScheurich 

Guess i didnt make myself clear i have to use an IF function . so is it possible to use the sumproduct inside the IF function??

[#4]

@Dev_1518 

=SUM(IF(B2:B6="Sales",1,0))

If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.

[#5]

@HansVogelaar  Sorry but your formula isint yielding the correct  answer 😞

[#6]

This =SUM(IF(B2:B6="Sales",1,0)) should give you correct result. Check you data either have leading or trailing space. Also this should work =SUM(--(B2:B6="Sales")).

[#7]

@Dev_1518 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

[#8]

@HansVogelaar 

 https://docs.google.com/spreadsheets/d/1PnbUq9nKv8jLcDJmqO52Be1i70WSTUWFYYoVmot1wvI/edit?usp=sharing

[#9]

@Dev_1518 

Thanks! This is what I see when I open the workbook:

The formula returns 2. Isn't that what you wanted?

[#10]

I have figured it out thanx a lot for all your inputs tho