Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count instances of a reference?
Hello all,
I have a spreadsheet project I need to complete for a spreadsheet with multiple worksheets (13 in total, 1 per month and a results page). Each of the monthly worksheets will contain the same type of data but this data will vary in quantity. it is for an IT support desk to calculate the number of times a certain field is populated for each person. Column's A, B & C are the same data in both worksheets. Worksheet 1 Column A Column B Column C Person 1 Field 1 Trigger Worksheet 2 Column A Column B Column C Person 1 Field 1 Trigger Using the data from Worksheet 2 how can I work out in Worksheet 1 how many 'Field 1' and 'Trigger' entries there were for 'Person 1'? Worksheet may contain tens of thousands of rows with thousands of entries per person but there should only be one entry for each person on Worksheet 1. Thanks in advance, A. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count instances of a reference?
Try one of these...
Excel 2007 (or later) only. (more efficient that the SUMPRODUCT version) =COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,B2,Sheet2!C:C,C 2) Any version of Excel: =SUMPRODUCT(--(Sheet2!A2:A100=A2),--(Sheet2!B2:B100=B2),--(Sheet2!C2:C100=C2)) Note that you can't use entire columns as range references with SUMPRODUCT unless you're using Excel 2007 or later. -- Biff Microsoft Excel MVP "Mercian" wrote in message ... Hello all, I have a spreadsheet project I need to complete for a spreadsheet with multiple worksheets (13 in total, 1 per month and a results page). Each of the monthly worksheets will contain the same type of data but this data will vary in quantity. it is for an IT support desk to calculate the number of times a certain field is populated for each person. Column's A, B & C are the same data in both worksheets. Worksheet 1 Column A Column B Column C Person 1 Field 1 Trigger Worksheet 2 Column A Column B Column C Person 1 Field 1 Trigger Using the data from Worksheet 2 how can I work out in Worksheet 1 how many 'Field 1' and 'Trigger' entries there were for 'Person 1'? Worksheet may contain tens of thousands of rows with thousands of entries per person but there should only be one entry for each person on Worksheet 1. Thanks in advance, A. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change All Instances of a Cell Reference in a Formula | Excel Discussion (Misc queries) | |||
count different instances in a list | Excel Discussion (Misc queries) | |||
Count Instances of value in two columns | Excel Worksheet Functions | |||
Count the number of Instances | Excel Discussion (Misc queries) | |||
Count Instances | Excel Discussion (Misc queries) |