Sumproduct help

[Brad]

I have a spreadsheet that I am updating everyday with hours based on a job by
job basis and I would like summarize this into a spreadsheet for total
hours/day by installer. Here's how my detailed sheet looks. (Name of Sheet
is 'July')

Column A contains date
Column G contains Installer name
Column I contains hours

In my summary Sheet (name of sheet is 'Payroll Sumarry') I have the Date in
Column A and each installer name across the top in Row 1 so for example cell
C1 contains the name of installer I want to add. How do I do this?

I tried using the following formula =SUMPRODUCT(--(July!A:A='Payroll
Summary'!A2),--(July!G:G=C1),July!I:I) But all I get back is #NUM! error.

 

[Tom Ogilvy]

Might be easier to build a pivottable to get this layout.

 

[Bernie Deitrick]

Brad,

Use a pivot table.

HTH,
Bernie
MS Excel MVP

 

[Brad]

do pivot tables update automatically?

Might be easier to build a pivottable to get this layout.

 

[Tom Ogilvy]

Not like a formula, but you just hit refresh data under the data menu. If
the extent of your data will change, then you can use a dynamic named range
as the source for the table.

 

[Brad]

I would prefer to have something that would update daily on it's own if
possible.

 

[Harlan Grove]

Brad wrote...
....

I tried using the following formula =SUMPRODUCT(--(July!A:A='Payroll
Summary'!A2),--(July!G:G=C1),July!I:I) But all I get back is #NUM! error.

The problem is that SUMPRODUCT doesn't support entire-column ranges.
You can use ranges or arrays with 65,535 rows, but not ones with 65,536
rows. On the assumption you'd be using a real database if you had to
keep track of +60K records like this, I'll assume you could get by with
rows 1 through 65535. Change your formula to

=SUMPRODUCT(--(July!$A$1:$A$65535=$A2),--(July!$G$1:$G$65535=C$1),
July!$I$1:$I$65535)

 

[Brad]

that seem to be working. Thanks

Brad wrote...
....

The problem is that SUMPRODUCT doesn't support entire-column ranges.
You can use ranges or arrays with 65,535 rows, but not ones with 65,536
rows. On the assumption you'd be using a real database if you had to
keep track of +60K records like this, I'll assume you could get by with
rows 1 through 65535. Change your formula to

=SUMPRODUCT(--(July!$A$1:$A$65535=$A2),--(July!$G$1:$G$65535=C$1),
July!$I$1:$I$65535)