Problem Summing in an Array

[Rachelplatt14]

Hello,

I imagine I'm making a pretty basic mistake, I'm completely self-taught via Google/various forums so my Excel knowledge is less than extensive.

I am using an array to sum the amount of work (in hours) completed in any particular week. There are four different types of work listed, each with a set amount of time allocated for completion. The array is checking for the correct date range (blue portion of formula) and then summing up the total time taken (red portion of formula)

=SUM(IF('UAP GAPS FOR SEARCHES'!$AG$2:$AG$4341>=0,IF('UAP GAPS FOR SEARCHES'!$AE$2:$AE$4341=B14,1,0),0))

The problem that I'm having is that the formula is only counting the whole numbers - nothing after the decimal place. For example it is summing;

1.25
1.25
2.75
2.75

as 6, rather than 8.

How can I improve my formula so that it sums correctly?

Many thanks in advance for all help given.

Rachel

 

[Aladin Akyurek]

Hello,

I imagine I'm making a pretty basic mistake, I'm completely self-taught via Google/various forums so my Excel knowledge is less than extensive.

I am using an array to sum the amount of work (in hours) completed in any particular week. There are four different types of work listed, each with a set amount of time allocated for completion. The array is checking for the correct date range (blue portion of formula) and then summing up the total time taken (red portion of formula)

=SUM(IF('UAP GAPS FOR SEARCHES'!$AG$2:$AG$4341>=0,IF('UAP GAPS FOR SEARCHES'!$AE$2:$AE$4341=B14,1,0),0))

The problem that I'm having is that the formula is only counting the whole numbers - nothing after the decimal place. For example it is summing;

1.25
1.25
2.75
2.75

as 6, rather than 8.

How can I improve my formula so that it sums correctly?

Many thanks in advance for all help given.

Rachel

Control+shift+enter, not just enter:

=SUM(
   IF('UAP GAPS FOR SEARCHES'!$AE$2:$AE$4341=B14,
   IF('UAP GAPS FOR SEARCHES'!$AG$2:$AG$4341>=0,
    'UAP GAPS FOR SEARCHES'!$AG$2:$AG$4341)))

On a 2007 system or later:

=SUMIFS(
  'UAP GAPS FOR SEARCHES'!$AG$2:$AG$4341,
  'UAP GAPS FOR SEARCHES'!$AE$2:$AE$4341,B14,
  'UAP GAPS FOR SEARCHES'!$AG$2:$AG$4341,">=0")