Sumproduct help
 

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.

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

--
Regards,
Tom Ogilvy

"Brad" wrote in message
...
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.

Brad,

Use a pivot table.

HTH,
Bernie
MS Excel MVP


"Brad" wrote in message
...
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.

do pivot tables update automatically?

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Brad" wrote in message
...
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.

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.

--
Regards,
Tom Ogilvy

"Brad" wrote in message
...
do pivot tables update automatically?

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Brad" wrote in message
...
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.

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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"Brad" wrote in message
...
do pivot tables update automatically?

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Brad" wrote in message
...
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.

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)

that seem to be working. Thanks

"Harlan Grove" wrote:

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)