| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| multiply formula where 1 cell has a (sumif) formula as a result | Excel Worksheet Functions | |||
| add value of 4 cells, multiply by 3 subtract 72 multiply by 80% | Excel Discussion (Misc queries) | |||
| Countif, then multiply?? | Excel Worksheet Functions | |||
| How can I set up a formula to multiply $ x hrs? i.e. 8:30 (8hr 30. | Excel Worksheet Functions | |||
| How do I create a formula that will multiply a 5% of a value >$150 | Excel Worksheet Functions | |||
Linear Mode
