| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Countif w/ more than one criteria
I need to count how many time a selection come up in my log.
Example: Tab 1: Log D E D 1 charge code WR Code Sub. code 2 HR Admin Miscellaneous 3 Facility Admin Miscellaneous 4 Facility Employees New Issue 5 Facility Temp New Issue 6 Facility Temp Miscellaneous Tab 2: Weekly Report 1 Totals: Formula I used: 2 HR =COUNTIF(Log!D:D,"HR")= 1 good 3 Facility =COUNTIF(Log!D:D,"Facility")= 3 4 Blank (two sections) 5 -Admin =COUNTIF(Log!E:E,"Admin")= 2 good 4 -Miscellaneous =(?) I need only the count that see both Miscellaneous and Temp to =1 How (If D is Miscellaneous and E is Temp then the count is 1) I tryied =countif(D:D,"Miscellaneous",E:E,"Temp") didn't work. Can you help? -- Susan Kelly |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excell Countif w/ more than one criteria
u
-- "SKelly" wrote in message ... I need to count how many time a selection come up in my log. Example: Tab 1: Log D E D 1 charge code WR Code Sub. code 2 HR Admin Miscellaneous 3 Facility Admin Miscellaneous 4 Facility Employees New Issue 5 Facility Temp New Issue 6 Facility Temp Miscellaneous Tab 2: Weekly Report 1 Totals: Formula I used: 2 HR =COUNTIF(Log!D:D,"HR")= 1 good 3 Facility =COUNTIF(Log!D:D,"Facility")= 3 4 Blank (two sections) 5 -Admin =COUNTIF(Log!E:E,"Admin")= 2 good 4 -Miscellaneous =(?) I need only the count that see both Miscellaneous and Temp to =1 How (If D is Miscellaneous and E is Temp then the count is 1) I tryied =countif(D:D,"Miscellaneous",E:E,"Temp") didn't work. Can you help? -- Susan Kelly |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excell Countif w/ more than one criteria
Use SUMPRODUCT.
=SUMPRODUCT(--(D1:D100="Miscellaneous"),--(E1:E100="Temp")) You have to specify the range with this. It cannot be an entire column. Does that help? Paul -- "SKelly" wrote in message ... I need to count how many time a selection come up in my log. Example: Tab 1: Log D E D 1 charge code WR Code Sub. code 2 HR Admin Miscellaneous 3 Facility Admin Miscellaneous 4 Facility Employees New Issue 5 Facility Temp New Issue 6 Facility Temp Miscellaneous Tab 2: Weekly Report 1 Totals: Formula I used: 2 HR =COUNTIF(Log!D:D,"HR")= 1 good 3 Facility =COUNTIF(Log!D:D,"Facility")= 3 4 Blank (two sections) 5 -Admin =COUNTIF(Log!E:E,"Admin")= 2 good 4 -Miscellaneous =(?) I need only the count that see both Miscellaneous and Temp to =1 How (If D is Miscellaneous and E is Temp then the count is 1) I tryied =countif(D:D,"Miscellaneous",E:E,"Temp") didn't work. Can you help? -- Susan Kelly |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excell Countif w/ more than one criteria
=SUMPRODUCT(N(D1:D1000="Temp"),N(E1:E1000="Miscell aneous"))
-- Gary''s Student - gsnu200796 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excell Countif w/ more than one criteria
what is "N"?
-- Susan Kelly "Gary''s Student" wrote: =SUMPRODUCT(N(D1:D1000="Temp"),N(E1:E1000="Miscell aneous")) -- Gary''s Student - gsnu200796 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excell Countif w/ more than one criteria
what is "--"?
-- Susan Kelly "PCLIVE" wrote: Use SUMPRODUCT. =SUMPRODUCT(--(D1:D100="Miscellaneous"),--(E1:E100="Temp")) You have to specify the range with this. It cannot be an entire column. Does that help? Paul -- "SKelly" wrote in message ... I need to count how many time a selection come up in my log. Example: Tab 1: Log D E D 1 charge code WR Code Sub. code 2 HR Admin Miscellaneous 3 Facility Admin Miscellaneous 4 Facility Employees New Issue 5 Facility Temp New Issue 6 Facility Temp Miscellaneous Tab 2: Weekly Report 1 Totals: Formula I used: 2 HR =COUNTIF(Log!D:D,"HR")= 1 good 3 Facility =COUNTIF(Log!D:D,"Facility")= 3 4 Blank (two sections) 5 -Admin =COUNTIF(Log!E:E,"Admin")= 2 good 4 -Miscellaneous =(?) I need only the count that see both Miscellaneous and Temp to =1 How (If D is Miscellaneous and E is Temp then the count is 1) I tryied =countif(D:D,"Miscellaneous",E:E,"Temp") didn't work. Can you help? -- Susan Kelly |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excell Countif w/ more than one criteria
This is a double urnary or double minus. It converts true/false to 1's and
0's. So each time it matches "Miscellaneous", it returns true and is coverted to 1. Same thing goes with "Temp". So if D1 returns True and E1 returns "False", then that converts to 1 and 0. Because this is SUMPRODUCT, the two are multiplied together. 1 * 0 = 0. But if both D1 and E1 return true, thats 1 and 1. 1 * 1 = 1. So each time the criteria is matched as desired, it will count as one. Hope this helps. Paul -- "SKelly" wrote in message ... what is "--"? -- Susan Kelly "PCLIVE" wrote: Use SUMPRODUCT. =SUMPRODUCT(--(D1:D100="Miscellaneous"),--(E1:E100="Temp")) You have to specify the range with this. It cannot be an entire column. Does that help? Paul -- "SKelly" wrote in message ... I need to count how many time a selection come up in my log. Example: Tab 1: Log D E D 1 charge code WR Code Sub. code 2 HR Admin Miscellaneous 3 Facility Admin Miscellaneous 4 Facility Employees New Issue 5 Facility Temp New Issue 6 Facility Temp Miscellaneous Tab 2: Weekly Report 1 Totals: Formula I used: 2 HR =COUNTIF(Log!D:D,"HR")= 1 good 3 Facility =COUNTIF(Log!D:D,"Facility")= 3 4 Blank (two sections) 5 -Admin =COUNTIF(Log!E:E,"Admin")= 2 good 4 -Miscellaneous =(?) I need only the count that see both Miscellaneous and Temp to =1 How (If D is Miscellaneous and E is Temp then the count is 1) I tryied =countif(D:D,"Miscellaneous",E:E,"Temp") didn't work. Can you help? -- Susan Kelly |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CountIf with 2 criteria possible? | Excel Discussion (Misc queries) | |||
| Countif with Two Criteria | Excel Worksheet Functions | |||
| count criteria excell | Excel Worksheet Functions | |||
| Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
| Countif using format criteria....not number criteria? | Excel Worksheet Functions | |||
Linear Mode
