| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count ifs - 2 conditions
Hi I have a list of data shown below, in 2 cloumns.
I want to do a count if to show the total people in each department and location i.e. COUNT the number of occurances of people in HEAD OFFICE and PRODUCTION and then the number of occurances of people in HEAD OFFICE and FINANCE and so on.... Many thanks Location Department Head Office Production Birmingham Sales London Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Sales Head Office Sales Head Office Sales Head Office Finance Head Office Sales Head Office Finance London Production London Production London Production London Production London Production London Sales London Sales London Sales London Sales London Finance London Finance Birmingham Production Birmingham Production Birmingham Production Birmingham Production |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count ifs - 2 conditions
A Pivot Table would work nicely here, or use SUMPRODUCT
Let your data be in A1:B100 In D2:D5 (or however many rows it takes) enter the department names In E1:K1 (or however many columns it takes) enter the location names In E2 enter =SUMPRODUCT(--($A$1:$A$100=$D2),--($B$1:$B$100=E$1) copy down and across you table best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Nick" wrote in message ... Hi I have a list of data shown below, in 2 cloumns. I want to do a count if to show the total people in each department and location i.e. COUNT the number of occurances of people in HEAD OFFICE and PRODUCTION and then the number of occurances of people in HEAD OFFICE and FINANCE and so on.... Many thanks Location Department Head Office Production Birmingham Sales London Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Sales Head Office Sales Head Office Sales Head Office Finance Head Office Sales Head Office Finance London Production London Production London Production London Production London Production London Sales London Sales London Sales London Sales London Finance London Finance Birmingham Production Birmingham Production Birmingham Production Birmingham Production |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count ifs - 2 conditions
With locations in cells A5:A35, and Departments in cells B5:B35, and
assuming an employee count by location/department in cells c5:c35... And assuming you have a summary section that looks like this in F5:G15 Birmingham Finance Birmingham Production Birmingham Sales Head Office Finance Head Office Production Head Office Sales London Finance London Production London Sales ....you can use this formula to summarize: =SUMPRODUCT(--(F5=$A$5:$A$35),--(G5=$B$5:$B$35),$C$5:$C$35) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count ifs - 2 conditions
Hi,
What you need is SUMPRODUCT. =SUMPRODUCT((A1:A10="head office")*(B1:B10="production")) HTH JG "Nick" wrote: Hi I have a list of data shown below, in 2 cloumns. I want to do a count if to show the total people in each department and location i.e. COUNT the number of occurances of people in HEAD OFFICE and PRODUCTION and then the number of occurances of people in HEAD OFFICE and FINANCE and so on.... Many thanks Location Department Head Office Production Birmingham Sales London Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Sales Head Office Sales Head Office Sales Head Office Finance Head Office Sales Head Office Finance London Production London Production London Production London Production London Production London Sales London Sales London Sales London Sales London Finance London Finance Birmingham Production Birmingham Production Birmingham Production Birmingham Production |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count ifs - 2 conditions
One option is to use a pivot table. Drag Location and Department into the
Row area and Count of Department into the data area and what you should see is: Count of Department Location Department Total Birmingham Production 4 Sales 1 Birmingham Total 5 Head Office Finance 9 Production 1 Sales 4 Head Office Total 14 London Finance 3 Production 5 Sales 4 London Total 12 Grand Total 31 -- Gary''s Student "Nick" wrote: Hi I have a list of data shown below, in 2 cloumns. I want to do a count if to show the total people in each department and location i.e. COUNT the number of occurances of people in HEAD OFFICE and PRODUCTION and then the number of occurances of people in HEAD OFFICE and FINANCE and so on.... Many thanks Location Department Head Office Production Birmingham Sales London Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Sales Head Office Sales Head Office Sales Head Office Finance Head Office Sales Head Office Finance London Production London Production London Production London Production London Production London Sales London Sales London Sales London Sales London Finance London Finance Birmingham Production Birmingham Production Birmingham Production Birmingham Production |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count ifs - 2 conditions
Wow, quick response!
Thanks everyone, works a treat! "Gary''s Student" wrote: One option is to use a pivot table. Drag Location and Department into the Row area and Count of Department into the data area and what you should see is: Count of Department Location Department Total Birmingham Production 4 Sales 1 Birmingham Total 5 Head Office Finance 9 Production 1 Sales 4 Head Office Total 14 London Finance 3 Production 5 Sales 4 London Total 12 Grand Total 31 -- Gary''s Student "Nick" wrote: Hi I have a list of data shown below, in 2 cloumns. I want to do a count if to show the total people in each department and location i.e. COUNT the number of occurances of people in HEAD OFFICE and PRODUCTION and then the number of occurances of people in HEAD OFFICE and FINANCE and so on.... Many thanks Location Department Head Office Production Birmingham Sales London Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Sales Head Office Sales Head Office Sales Head Office Finance Head Office Sales Head Office Finance London Production London Production London Production London Production London Production London Sales London Sales London Sales London Sales London Finance London Finance Birmingham Production Birmingham Production Birmingham Production Birmingham Production |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Advanced unique cell count with multiple conditions ... help! | Excel Worksheet Functions | |||
| Count the number of Cells in one ROW with conditions | Excel Worksheet Functions | |||
| returning a count if two conditions are met | Excel Worksheet Functions | |||
| count unique with conditions | Excel Worksheet Functions | |||
| How do I count number of cels the matches 2 conditions ? | Excel Worksheet Functions | |||
Linear Mode
