SUM & Array Functions

[Charlie Thompson]

I have tried to construct an array function in order to sum a column of
cells only if the amount meets two criteria.

Here are the particulars:

I have two worksheets

Worksheet 1 is named "7-31-04"
Worksheet 2 is named "Payroll Register"

I am trying to sum the amount in a Gross Pay column only if it meets the
following criteria:

Employee Name on Payroll Register worksheet = Employee Name on 7-31-04
worksheet
Other Type on Payroll Register worksheet = "CO2" on 7-31-04 worksheet

Here is the formula as I have it now:

=SUM(('Payroll Register'!C2:C129='7-31-04'!B8)*('Payroll
Register'!P2129='7-31-04'!C8)*'Payroll Register'!S2:S129)

Where 'Payroll Register'!C2:C129 is the list of employees and '7-31-04'!B8
is the employee I'm matching

Where 'Payroll Register'!P2129 is the list of Other Types and '7-31-04'!C8
is the "CO2" code

And 'Payroll Register'!S2:S129 is the list of amounts that I need summed if
the two above criteria are met.

I have retyped and retyped this formula and used the CTRL+SHIFT+ENTER to
enter it, but I keep getting the #VALUE result.

I have even tried the SUM function in an array using only one criteria and
keep getting the #VALUE result.

Thanks for anyone's help out there.

Charlie

 

[Frank Kabel]

Hi
try (non array entered)
=SUMPRODUCT(--('Payroll Register'!C2:C129='7-31-04'!B8),--('Payroll
Register'!P2129='7-31-04'!C8),'Payroll Register'!S2:S129)

Make sure column S does not contain error values