Prev: Sum multiple vlookup returns across multiple columns
Next: Subtotal across a row
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:F6CB99A3-EF54-455C-892C-FB6CC0EF82A9(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