|
Multiply CountIf in one formula
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(D2:D6,"NY") Obviously its calculating both fields where its true and adding them together which is not what i want :) Pls help! Cheers! Wayne |
Multiply CountIf in one formula
Try this:
=SUMPRODUCT(--(C2:C6="IT"),--(D2:D6="NY")) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "ant1983" wrote in message ... 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(D2:D6,"NY") Obviously its calculating both fields where its true and adding them together which is not what i want :) Pls help! Cheers! Wayne |
Multiply CountIf in one formula
Damn, thanks yeah man!
"Ron Coderre" wrote: Try this: =SUMPRODUCT(--(C2:C6="IT"),--(D2:D6="NY")) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "ant1983" wrote in message ... 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(D2:D6,"NY") Obviously its calculating both fields where its true and adding them together which is not what i want :) Pls help! Cheers! Wayne |
Multiply CountIf in one formula
I'm glad I could help.....(thanks for the feedback)
*********** Regards, Ron XL2003, WinXP "ant1983" wrote: Damn, thanks yeah man! "Ron Coderre" wrote: Try this: =SUMPRODUCT(--(C2:C6="IT"),--(D2:D6="NY")) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "ant1983" wrote in message ... 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(D2:D6,"NY") Obviously its calculating both fields where its true and adding them together which is not what i want :) Pls help! Cheers! Wayne |
| All times are GMT +1. The time now is 12:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com