Index formula NUM# error

[blossomthe2nd]

Hi guys

I have an excel spreadsheet where the data is inported from a PRN file.

I wish to do an Index formula to make a summary based on specific criteria.

One of which is date

On Sheet1 colum A I have a list of dates , on sheet2 is the data I wish to retrive my query from, I have tried the below sumproduct but keep getting Num# error

=SUMPRODUCT(--('Master Result'!E:E=A4),--('Master Result'!P:P="John"))

I believe its down to how the dates are formatted and have changed both to dates but still not working ,
Can anyone advise please ?

Thanks

 

[Jonmo1]

If you have XL2003 or earlier, you cannot use entire column references like E:E in sumproduct.
You have to specify row#s like E1:E1000

And you must keep equal number of rows in each reference.


Hope that helps.

 

[blossomthe2nd]

Thanks Jonmo, Rookie mistake, one quick one , I have amended to

=SUMPRODUCT(--('Master Result'!E1:E300000=A4),--('Master Result'!P1:P300000="John"))

But now getting a NAME? error, this column for the name P:P is derived from a formula also .... Could this be why ? Any idea how to fix ?

thanks,

 

[Jonmo1]

What version of Excel do you have?
Because you've now written the formula for 300,000 rows.
Versions prior to 2007 did not have that many rows.

If you have XL2007 or higher, you CAN use entire column refs like E:E
Although it's not recommended, but it is allowed.

 

[blossomthe2nd]

Hi Jonmo,

Sorry was out of office for a couple of days.
The below is the formula I'm using , the first part is Date , I am getting it to work, but the second part re the John I still am getting a N/A# error.

The John in Master result is derived from a formula would this be the reason ?

Thanks

Anne

=SUMPRODUCT(--('Master Result'!E1:E10000=A4),--('Master Result'!P1:P10000="John"))