Countif or sum product?

[gazzo]

i want to return a sum total in a single cell based of two criteria.


basically
on another work sheet i want to collate my info

If column G on sheet 'Area 7 & Ladywood addresses' has "GREEN" in it and column J has "completed" then i want it to count.
if column G on sheet 'Area 7 & Ladywood addresses' has "GREEN" in it and column J is blank then i want it to not count.

Thanks for help people

[mdbct]

The following will count cells according to your criteria. Adjust the range to suit your sheet. You cannot reference the entire column and the two ranges must be the same size.

=SUMPRODUCT(('Area 7 & Ladywood addresses'!G1:G10000="Green")*('Area 7 & Ladywood addresses'!J1:J10000="Completed"))