Weird Sumproduct issue..

[Timmy Mac1]

Well I find it weird anyway.... here it is

My data has two columns...Year and Reference in cells A1 and B1 respectively

In the first row, under the headings I have the data

Year Ref
2004 - X123

Using the Sumif function gives the following result..

=SUMIF(REF,B2,YEAR) = 2004

But...

SUMPRODUCT((REF=B2)*YEAR)gives only #VALUE!

Now as I'm only testing the data at present I will eventually be lloking at results dependent on more than one column of data, so I need the SUMPRODUCT function to be effective.

I've been aware of similar problems in the past working with imported data so I set up an additional column entitled VALUES into which I posted in cell C2 the formula =VALUE(A2)

I then tried the SUMPRODUCT function again using the VALUES field instead of the YEAR field and got the answer of 2004 that I was looking for.

However, though I then copied C2 as a value into A2, and then subsequently copy and paste valued C2 on its own and then copied it to A2, I still cannot get the SUMPRODUCT to recognise the 2004 figure in cell A2 as a value.

Of course I have an obvious getaround, but it would be much easier for me to be able to get SUMPRODUCT to recognise the original data somehow.

Any thoughts, suggestions, explanations?

Many thanks

TM

[Vito]

Sumproduct() does not allow you to use full column references like A:A, or named ranges referring to full columns....Sumif() does allow it.