help to amend a sumproduct formula to minus 'Yes' from another column

Kellis

New member
Joined
Sep 27, 2013
Messages
1
Reaction score
0
Points
0
Location
Scotland
Hello Everybody

First time on this forum so a big hi and thanks hopefully in advance. Self taught excel user who needs lots of help.

I have a formula

=IF(SUMPRODUCT(('Metering Jobs'!$P$2:$P$20000=B9)*('Metering Jobs'!$B$2:$B$20000=Summary!$M$8))=0,"0",SUMPRODUCT(('Metering Jobs'!$P$2:$P$20000=B9)*('Metering Jobs'!$B$2:$B$20000=Summary!$M$8)))
Which works great, the formula counts all the jobs however I have added another column in L which is cancelled jobs. I need the formula to subtract or ignore the count if the word 'yes' is typed in column L. I was thinking of having a validation yes / no in column L unsure which would work better.

Anyone?

Thanks
 
Top