Thread: Sum/match function error (pointed date cell)

Hello,

I occurred a problem with a sum/match function, which I thought, would be right. Unfortunately when I pressed enter to finish the formula excel told me it has an error and pointed me to the part with connection to the cell with a date that I was referring to.

I have attached a sample book and here is a code that I used in cell F28.

=if($C28<=sum('Sheet2!'$G$27-3),sum(vlookup('Sheet2!'$B28,'Sheet1!'$B$5:$G$24,2,false)-sum(index('Sheet1'!$F$31:$AI$50,match('Sheet2!'$B28,'Sheet1'!$B$31:$B$50,0),match('Sheet2!'$C28,'Sheet1!'$F$29:$T$29,0),match('Sheet2!'$E28,'Sheet1!'$F$30:$T$30,0)):index('Sheet1!'$F$31:$T$50,match('Sheet2!'$B28,'Sheet1!'$B$31:$B$50,0),match(sum('Sheet2!'$G$27-3),'Sheet1!'$F$29:$T$29,0),match('Sheet2!'$T$1,'Sheet1!'$F$30:$T$30,0)))),sum(vlookup('Sheet2!'$B28,'Sheet1!'$B$5:$G$24,2,false)+sum(index('Sheet1!'$U$31:$AI$50,match('Sheet2!'$B28,'Sheet1!'$B$31:$B$50,0),match('Sheet2!'£G$27,'Sheet1!'$U$29:$AI$29,0),match('Sheet2!'$T$2,'Sheet1!'$U$30:$AI$30,0)):index('Sheet1!'$U$31:$AI$50,match('Sheet2!'$B28,'Sheet1!'$B$31:$B$50,0),match('Sheet2!'$C28,'Sheet1!'$U$29:$AI$29,0),match('Sheet2!'$E28,'Sheet1!'$U$30:$AI$30,0)))))

If anyone knows what to change please let me know so I understand where I have done a mistake and which direction I shall go in.

What I am trying to achieve is to match the last received date with the deliveries over last two weeks and first check if it is before the start of the current week. If so the I want it to compare with the deliveries date and sum all the deliveries from the next vehicle than the one that is shown on the left in the table (column E) to the end of that previous week. If it is in the current week I want deliveries from the beginning of the current week to the vehicle stated in the column E to be added to the arrears from sheet 1 column C.

I have managed to include the stated vehicle number in the sum if the last delivery is in previous week but I don’t know how to exclude it. I also don’t know if that sum function is right with all those match arguments.

Anyone with an idea of how to fix it, please help.
I am cracking my head over it.

Thanks for having a look

Ramzes, please stick to the forum font defaults. Place long formulas in code tags. That will make your posts much more readable and trigger more replies.

thanks.

Have you tried inspecting your formula with the Evaluate Formula tool? There you can see at which step of the evaluation the formula gets an error.

Ramzes, I'm not in front of 2003 at the moment, so my recollection about the Evaluation Tool in the menus is a bit hazy. I have it as an Icon in the toolbar

select the cell, click Tools - Auditing - Evaluate Formula

if I recall correctly. This will open up a dialog where you can step through the formula as it evaluates one bit at a time.