Multiply CountIf in one formula

[ant1983]

Hi,

Sure this is a no brainer for any1 out there reading this but i just cant
seem to do it:

Basically what i want to do is count the number of times a certain word is
repeated in one field and then within that another word in another field...
Maybe thats not to clear so lets try an example:

This is what my excel sheet looks like:

A B C D E
1 Name Age Dept City Country
2 Wayne 24 HR JHB RSA
3 Jack 26 IT NY USA
4 Sam 28 IT JHB RSA
5 Harry 30 Sales LA USA
6 John 31 IT LA USA

So, what i want to see is how many people are in the IT Department in NY.
AS you can see from the table above there are 3 IT people but only 1 is in
NY. What should my formula look like??

This is what i have but its obviously wrong:
=COUNTIF(C2:C6,"IT")+COUNTIF(D26,"NY")

Obviously its calculating both fields where its true and adding them
together which is not what i want

Pls help!

Cheers!

Wayne

 

[Ron Coderre]

Try this:
=SUMPRODUCT(--(C2:C6="IT"),--(D26="NY"))

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[ant1983]

Damn, thanks yeah man!

Try this:
=SUMPRODUCT(--(C2:C6="IT"),--(D26="NY"))

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Coderre]

I'm glad I could help.....(thanks for the feedback)

***********
Regards,
Ron

XL2003, WinXP