Array formula with =sum
 

The first 7000 of wages are taxable for federal unemployment. I have two
columns that represent wages for Quarter 1 and Quarter 2:
A B
5000 2500
8000 6000
6000 750

Column A has an array formula that gives me the sum of the lesser of 7000
or each of the amounts in column A. That formula is
{=SUM(IF(A1:A3<7000,A1:A3,7000))}. I need an array formula in column B that
returns the sum of the taxable unemployment wages for the quarter. A
non-array formula for each row would be =MIN(MAX(7000-A1,0),B1). What array
formula will return the sum of the taxable unemployment wages for column B,
taking into account the wages paid in column A??

Array formula with =sum
 

Hi
try the array formula
=SUM(IF(7000-A1:A3<B1:B3,IF(7000-A1:A3<0,0,7000-
A1:A3),B1:B3))

-----Original Message-----
The first 7000 of wages are taxable for federal
unemployment. I have two
columns that represent wages for Quarter 1 and Quarter 2:
A B
5000 2500
8000 6000
6000 750

Column A has an array formula that gives me the sum of
the lesser of 7000
or each of the amounts in column A. That formula is
{=SUM(IF(A1:A3<7000,A1:A3,7000))}. I need an array
formula in column B that
returns the sum of the taxable unemployment wages for the
quarter. A
non-array formula for each row would be =MIN(MAX(7000-
A1,0),B1). What array
formula will return the sum of the taxable unemployment
wages for column B,
taking into account the wages paid in column A??


.

Array formula with =sum
 

Thanks, Frank - it worked perfectly.


"Frank Kabel" wrote in message
...
Hi
try the array formula
=SUM(IF(7000-A1:A3<B1:B3,IF(7000-A1:A3<0,0,7000-
A1:A3),B1:B3))

-----Original Message-----
The first 7000 of wages are taxable for federal
unemployment. I have two
columns that represent wages for Quarter 1 and Quarter 2:
A B
5000 2500
8000 6000
6000 750

Column A has an array formula that gives me the sum of
the lesser of 7000
or each of the amounts in column A. That formula is
{=SUM(IF(A1:A3<7000,A1:A3,7000))}. I need an array
formula in column B that
returns the sum of the taxable unemployment wages for the
quarter. A
non-array formula for each row would be =MIN(MAX(7000-
A1,0),B1). What array
formula will return the sum of the taxable unemployment
wages for column B,
taking into account the wages paid in column A??


.