From: Mercian on 26 Feb 2010 11:04 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.
From: T. Valko on 26 Feb 2010 12:01 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,C2) 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" <Mercian(a)discussions.microsoft.com> wrote in message news:F6CB99A3EF54455C892CFB6CC0EF82A9(a)microsoft.com... > 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.

Pages: 1 Prev: Sum multiple vlookup returns across multiple columns Next: Subtotal across a row 