The formula works except in one cell?

[err504]

I have a formula to audit some data which calculates correctly except in one cell.

I created a simplified spreadsheet as an example. The purpose is to compare the data on the $DailyLog sheet (Light blue columns on left) against the data on the $WeeklyPaystub sheet (Light yellow columns in center). The audit is done in the light gray columns on the right.

Column G21 should match F21, and H21 should say “OK”.

The formula for G21 is

=IF($D21="","",SUMPRODUCT(($A$2:$A$5000)=$D21,$B$2:$B$5000))

The formula for H21 is

=IF($D21="","",IF(SUM($F21-$G21)=0,"OK",SUM($F21-$G21)))

Everything else has been converted to number data.

I wanted to upload an attachment of the simplified sample spreadsheet, but the site told me the file was too large. In actuality, the site claims the maximum file size is 1,048,576 kb aka 1,024 mb. My sample spreadsheet is only 19.8 mb.

Here’s the attachment on a file sharing service. It will only be there 7 days before the service deletes it.

Why is cell G21 the only cell not adding up correctly?

[mikekaganski]

Why do you say it’s not adding up correctly? your second screenshot shows 6 rows with “work week” 201852 - A2 to A7 (and two with “work week” 201952 - A8 and A9). The corresponding sum of values in B2:B7 is 44.28 + 31.32 + 57.24 + 28.62 = 161.46, which matches perfectly what you have in G2; and it mismatches F2 - correctly giving you the difference of 32.94.

[err504]

Apparently being overtired, I am also blind. My human error failed to notice that there were 2 years for Work Week 52: 2018, and 2019 as pointed out by @mike-kaganski.

The error is not where I thought it was, but rather in the formula for $DailyLog.$A which calculates the Work Week and apparently fudges the year if the Work Week straddles the end of one year and the start of another.