bj and roger, thanks for input. I used the below.

=SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12"),--(MONTH('Imported
Data'!$I$2:$I$30000)=H5),--(YEAR('Imported
Data'!$I$2:$I$30000)=J5))-SUMPRODUCT(--('Imported
Data'!$C$2:$C$30000=6),--(MONTH('Imported
Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5))

It is returning a negative figure. Can i use two instances of sumproduct. I
presume so as neither said i couldn't. I have double checked my data and I am
still getting negatives

--
Regards vipa


"bj" wrote:

I am not a hundred percent sure this is what you want, but do you also want
the priority 6 ones to have the 12 in column A?
if so try

=SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"),--(MONTH('Imported
Data'!$I$2:$I$30000)=H5),--(YEAR('Imported
Data'!$I$2:$I$30000)=J5),--('Imported Data'!$C$2:$C$30000<6))

in not try
=SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"),--(MONTH('Imported
Data'!$I$2:$I$30000)=H5),--(YEAR('Imported
Data'!$I$2:$I$30000)=J5))-SUMPRODUCT(--('Imported
Data'!$C$2:$C$30000=6),--(MONTH('Imported
Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5))

You were right it was brackets.
"vipa2000" wrote:

I have he following code. i want to count all the 12's in columnA range
2-30000 on a month picked in cell H5 and a year J5 and then deduct those
records where priority = 6 and month =h5 and year =j5. Just cannot get the
thing to work. i am sure it is the brackets.

=SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"),--(MONTH('Imported
Data'!$I$2:$I$30000)=H5),--(YEAR('Imported
Data'!$I$2:$I$30000)=J5)-SUMPRODUCT(--('Imported
Data'!$C$2:$C$30000=6),--(MONTH('Imported
Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5)))

thanks in advance
--
Regards vipa