sumproduct or sumif?
 

Excel 2003

On the first sheet (FY2007MonthlyCosts), I have names in column A. Column
B, D, F, etc has hours. Column C, E, G has dollars. I sum the hours and
dollars in cells IU and IV with this:
SUMIF($B$4:$IT$4,"hours",B5:IT5)
SUMIF($B$4:$IT$4,"Dollars",B5:IT5)

These are copied down for each name (row).

On the second sheet (FY2007Summary), I use another sumif to find the data by
name.
SUMIF('FY2007Monthly Costs'!A5:A38,FY2007Summary!A6,'FY2007Monthly
Costs'!IU5:IU38)

This works but I was looking for an easier one step formula to use on the
Summary sheet.

sumproduct or sumif?
 

Hours =VLOOKUP(A6,'FY2007Monthly Costs'!A5:IV38,255,0)
Dollars = VLOOKUP(A6,'FY2007Monthly Costs'!A5:IV38,256,0)

To make it even easier, rename 'FY2007Monthly Costs'!A5:IV38 to a named
range (like EntireList) then your vlookups can just be:
=VLOOKUP(A6,EntireList,255,0)
=VLOOKUP(A6,EntireList,256,0)

Note that if the value in A6 does not appear in your master list, you'll get
an #N/A! error.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"SixBowls" wrote:

Excel 2003

On the first sheet (FY2007MonthlyCosts), I have names in column A. Column
B, D, F, etc has hours. Column C, E, G has dollars. I sum the hours and
dollars in cells IU and IV with this:
SUMIF($B$4:$IT$4,"hours",B5:IT5)
SUMIF($B$4:$IT$4,"Dollars",B5:IT5)

These are copied down for each name (row).

On the second sheet (FY2007Summary), I use another sumif to find the data by
name.
SUMIF('FY2007Monthly Costs'!A5:A38,FY2007Summary!A6,'FY2007Monthly
Costs'!IU5:IU38)

This works but I was looking for an easier one step formula to use on the
Summary sheet.

sumproduct or sumif?
 

Thanks. That works but I was trying to get a formula that would work without
doing the SumIf on the monthly costs tab.

"Queso" wrote:

Hours =VLOOKUP(A6,'FY2007Monthly Costs'!A5:IV38,255,0)
Dollars = VLOOKUP(A6,'FY2007Monthly Costs'!A5:IV38,256,0)

To make it even easier, rename 'FY2007Monthly Costs'!A5:IV38 to a named
range (like EntireList) then your vlookups can just be:
=VLOOKUP(A6,EntireList,255,0)
=VLOOKUP(A6,EntireList,256,0)

Note that if the value in A6 does not appear in your master list, you'll get
an #N/A! error.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"SixBowls" wrote:

Excel 2003

On the first sheet (FY2007MonthlyCosts), I have names in column A. Column
B, D, F, etc has hours. Column C, E, G has dollars. I sum the hours and
dollars in cells IU and IV with this:
SUMIF($B$4:$IT$4,"hours",B5:IT5)
SUMIF($B$4:$IT$4,"Dollars",B5:IT5)

These are copied down for each name (row).

On the second sheet (FY2007Summary), I use another sumif to find the data by
name.
SUMIF('FY2007Monthly Costs'!A5:A38,FY2007Summary!A6,'FY2007Monthly
Costs'!IU5:IU38)

This works but I was looking for an easier one step formula to use on the
Summary sheet.

sumproduct or sumif?
 

I think the way you're doing it is fine. SUMPRODUCT is only needed when you
have more than one criteria (so "hours" and perhaps "dates" too, or "hours"
and a specific person...your need is already limited to a single row, so
there's only 1 criteria and SUMIF is fine).
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"SixBowls" wrote:

Thanks. That works but I was trying to get a formula that would work without
doing the SumIf on the monthly costs tab.

"Queso" wrote:

Hours =VLOOKUP(A6,'FY2007Monthly Costs'!A5:IV38,255,0)
Dollars = VLOOKUP(A6,'FY2007Monthly Costs'!A5:IV38,256,0)

To make it even easier, rename 'FY2007Monthly Costs'!A5:IV38 to a named
range (like EntireList) then your vlookups can just be:
=VLOOKUP(A6,EntireList,255,0)
=VLOOKUP(A6,EntireList,256,0)

Note that if the value in A6 does not appear in your master list, you'll get
an #N/A! error.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"SixBowls" wrote:

Excel 2003

On the first sheet (FY2007MonthlyCosts), I have names in column A. Column
B, D, F, etc has hours. Column C, E, G has dollars. I sum the hours and
dollars in cells IU and IV with this:
SUMIF($B$4:$IT$4,"hours",B5:IT5)
SUMIF($B$4:$IT$4,"Dollars",B5:IT5)

These are copied down for each name (row).

On the second sheet (FY2007Summary), I use another sumif to find the data by
name.
SUMIF('FY2007Monthly Costs'!A5:A38,FY2007Summary!A6,'FY2007Monthly
Costs'!IU5:IU38)

This works but I was looking for an easier one step formula to use on the
Summary sheet.