SUM(IF) only finds first value in an array, sometimes

Question

I've got an excel spreadsheet with 2 sheets, sheet1 is an export from a sharepoint timesheet system, sheet2 is to be used for reporting purposes, mainly showing a breakdown of time on a week by week basis.

I need a formula which SUM the hours values (sheet1!G:G) where the week starting date column in sheet1 (sheet1!AI:AI, calculated using =D2-WEEKDAY(D2-1)) matches the week starting row in sheet2 (row 3).

So far I have tried SUMIF, SUMPRODUCT and now a SUM(IF) formula and all have the same problem, they either don't find values to SUM (values which are definitely there) or it will only SUM the first value in the array. For example, for weekstarting "12/06/2016" I should have a total of 336 hours but currently it only finds the first value in the list, 14. The different formula's I have tried using so far are as follows:

{=SUM(IF(sheet1!$AI$2:$AI$300=CB$3,sheet1!$G$2:$G$300,0))}

=SUMIF(sheet1!AI$2:AJ$300, CB$3, sheet1!G$2:G$300)

=SUMPRODUCT( -- (sheet1!$AI$2:$AI$300 = CB$3), sheet1!$G$2:$G$300)

All formula's have the same issue and produce the same values for each column in sheet 2 which makes me suspect it's something to do with my weekstarting calculation/array but I just can't work out what's causing it. Example of values produced currently below and expected values below that:

 05/06/2016 12/06/2016  19/06/2016  26/06/2016
 0.00       14.00       0.00        11.00

 05/06/2016 12/06/2016  19/06/2016  26/06/2016
 82.85      336.00      297.75      307.25

Example file with data and formulas - https://www.dropbox.com/s/5c2c5tyl993vxyt/SUMIF%20Example.xlsx?dl=0

Something is not matching up. Please provide examples of your data that demonstrate the problem. Read the HELP topics for How to create a Minimal, Complete, and Verifiable example — Ron Rosenfeld
I've included a dropbox link to a stripped down spreadsheet in the question — Curtis Elliott

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2016-07-25T11:46:00

Your Date columns on sheet one include both a date and a time component.

One fix: Change your week starting formula to:

=INT(A2-WEEKDAY(A2-1))

Excel stores Dates as integers with 1 = 1 Jan 1900. Times are stored as fractions of a day. So, for example, your first entry in Sheet1!A2 is really:

6/6/2016 10:27:37 AM

stored as

42527.4358449074

By removing the fractional part, you can then match with your week starting data on sheet2 and obtain the desired results.

Your SUMIF and SUMPRODUCT functions will also work properly now.

SUM(IF) only finds first value in an array, sometimes

1 Answers