Results 1 to 2 of 2

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

  1. #1
    Neophyte Kellis's Avatar
    Join Date
    Sep 2013
    Location
    Scotland
    Posts
    1
    Articles
    0

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •