SUMPRODUCT Problems

Hi all,

I'm working with a SUMPRODUCT formula looking looking at a Batch number, a contractor, a time period and an amount billed. The formula, which isn't working, looks like this:

Please Login or Register  to view this content.

To clarify - the range on column C is the batch number, the contractor is on column V (SSSP), the date is on column CA, and the amount billed is on BY. I'm getting a #VALUE! returned off this, and can't see exactly what the problem is.

Having done a few tests I think the problem lies somewhere in the dates and/or billing columns, but beyond that I'm stuck.

Any ideas appreciated.

TIA,

SamuelT

There are indeed. I take it that SUMPRODUCT won't work with errors, or I'll have to apply some sort of IF(ISERROR appendment?

Thanks,

SamuelT

Originally Posted by SamuelT

Hi all,

I'm working with a SUMPRODUCT formula looking looking at a Batch number, a contractor, a time period and an amount billed. The formula, which isn't working, looks like this:

Please Login or Register  to view this content.

To clarify - the range on column C is the batch number, the contractor is on column V (SSSP), the date is on column CA, and the amount billed is on BY. I'm getting a #VALUE! returned off this, and can't see exactly what the problem is.

Having done a few tests I think the problem lies somewhere in the dates and/or billing columns, but beyond that I'm stuck.

Any ideas appreciated.

TIA,

SamuelT

Hi,

It seems to work OK for me. I've recreated it exactly as you indicate. At first the date looked a little odd with '107' as the year parameter, but it worked and the result didn't change when I altered it to 2007.

Does row 12 have any column labels, or is this the start of the data proper?

The only thing I can think of is that you have some cells in column BY that have spaces in them or alpha characters. Check them with an =ISNUMBER(BY12) function in another column and copy down. They should all return TRUE. I'm using a Mac and if I put an alpha character I get a circular argument reference - why I'm not sure, but the formula still evaluates nevertheless. Maybe on a PC this returns an ERROR.

If you've got no alpha character in BY and the problem is still there, zip it up and attach here so that we can take a look.

Rgds

Hi,

It was the errors in the ranges. Not many, but they've been rectified now.

Many thanks for the assist,

SamuelT