SUMPRODUCT Multiple Criteria

[Henry c]

Hi,

I am trying to create a formular for fleet data.

The formular need to add all the data from each month which i have used the following formular to achieve:

= SUMPRODUCT((A2:A1001>=DATEVALUE("01/02/2009"))*(A2:A1001<=DATEVALUE("28/02/2009")),D2:D1001)

However i would like the formular to have a second criteria which would be the vehicle registration number. Therefore it would only add the data if for example the vehicle registration number was X and the data was in the month of January.

Does anybody know how I can ammend my formular to achieve this?

Thanks,

Henry

[DonkeyOte]

Welcome to the Board.

You don't specify the Reg location, nor if the Reg is partial match or not but in essence

=SUMPRODUCT(--($A2:$A$10001-DAY($A$2:$A$1001)+1=DATE(2009,1,1)),--(LEFT($B$2:$B$1001,1)="X"),$D$2:$D$1001)

above assumes values in A2 are date values or blank and that reg plates are stored in Column B

[Henry c]

Hi, Thanks for the quick response,

Sorry, my Reg data is in column E. And will be an exact match to what is in the formular

So i have ammended to...

=SUMPRODUCT(--($A2:$A$5000-DAY($A$2:$A$5000)+1=DATE(2009,1,1)),--(LEFT($E$2:$E$5000,1)="GL06WHG"),$D$2:$D$5000)



So this should total all the milage data for van GL06WHG in January, However I am getting a Zero value, but there is definatly data there, is there anything obvious I am doing wrong?

Thanks,

Henry

[DonkeyOte]

The LEFT(string,1) looks only at the leftmost character - given it is looking at one character only it will never = GL06WHG

For an exact match remove the LEFT, ie:

=SUMPRODUCT(--($A2:$A$5000-DAY($A$2:$A$5000)+1=DATE(2009,1,1)),--($E$2:$E$5000="GL06WHG"),$D$2:$D$5000)

[Henry c]

Thankyou very much, Ive never seen the LEFT term before, works a treat now!

Thanks Again,

Henry