How do I count instances of a reference?

[Mercian]

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.

 

[T. Valko]

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.