Cell references in SUMIF formulas
 

Hi,

I'm trying to create a timesheet for myself in Excel. At the top of the
sheet is a table with a cell for each day of the month, all with an empty
cell next to them.

Below this, is an area for entering job details, including the date of the
job and its duration in hours. In the empty cell next to each date, I want to
put a formula which says: "check the date column below, if the date
corresponds to that in the cell to your left, add the numbers of hours noted
on that line to this day's total".

At the moment, my formula looks like this:

=SUMIF($A$14:$A$71,"&B4",$F$14:$F$71)

If I take the ,"&B4" reference out and simply insert the relevant date, the
formula works fine. But I don't want to manually enter the date every time I
create a new page (for a new month). I want this to be a formula that's
robust enough to be copied and pasted.

Can anyone tell me where I'm going wrong.

Many thanks

Karl

Cell references in SUMIF formulas
 

Don't put quotes around your cell reference:

=SUMIF($A$14:$A$71,B4,$F$14:$F$71)

You only need quotes if you want to do some other comparison, and then
not around the cell ref, eg:

=SUMIF($A$14:$A$71,"<"&B4,$F$14:$F$71)

This checks for less than the value in B4.

Hope this helps.

Pete

On Jul 24, 12:46*pm, Karl wrote:
Hi,

I'm trying to create a timesheet for myself in Excel. At the top of the
sheet is a table with a cell for each day of the month, all with an empty
cell next to them.

Below this, is an area for entering job details, including the date of the
job and its duration in hours. In the empty cell next to each date, I want to
put a formula which says: "check the date column below, if the date
corresponds to that in the cell to your left, add the numbers of hours noted
on that line to this day's total".

At the moment, my formula looks like this:

=SUMIF($A$14:$A$71,"&B4",$F$14:$F$71)

If I take the ,"&B4" reference out and simply insert the relevant date, the
formula works fine. But I don't want to manually enter the date every time I
create a new page (for a new month). I want this to be a formula that's
robust enough to be copied and pasted.

Can anyone tell me where I'm going wrong.

Many thanks

Karl

Cell references in SUMIF formulas
 

Hi Pete, just wanted to say thanks very much. That was exactly what I was
after.

Cheers

Karl

"Pete_UK" wrote:

Don't put quotes around your cell reference:

=SUMIF($A$14:$A$71,B4,$F$14:$F$71)

You only need quotes if you want to do some other comparison, and then
not around the cell ref, eg:

=SUMIF($A$14:$A$71,"<"&B4,$F$14:$F$71)

This checks for less than the value in B4.

Hope this helps.

Pete

On Jul 24, 12:46 pm, Karl wrote:
Hi,

I'm trying to create a timesheet for myself in Excel. At the top of the
sheet is a table with a cell for each day of the month, all with an empty
cell next to them.

Below this, is an area for entering job details, including the date of the
job and its duration in hours. In the empty cell next to each date, I want to
put a formula which says: "check the date column below, if the date
corresponds to that in the cell to your left, add the numbers of hours noted
on that line to this day's total".

At the moment, my formula looks like this:

=SUMIF($A$14:$A$71,"&B4",$F$14:$F$71)

If I take the ,"&B4" reference out and simply insert the relevant date, the
formula works fine. But I don't want to manually enter the date every time I
create a new page (for a new month). I want this to be a formula that's
robust enough to be copied and pasted.

Can anyone tell me where I'm going wrong.

Many thanks

Karl

Cell references in SUMIF formulas
 

You're welcome, Karl - thanks for the feedback.

Pete

On Jul 25, 12:07*pm, Karl wrote:
Hi Pete, just wanted to say thanks very much. That was exactly what I was
after.

Cheers

Karl