+ Reply to Thread
Results 1 to 6 of 6

Help to amend sumproduct formula to minus depending on entry in another column

  1. #1
    Registered User
    Join Date
    03-16-2012
    Location
    PERTH
    MS-Off Ver
    Excel 2007
    Posts
    39

    Help to amend sumproduct formula to minus depending on entry in another column

    Hello Everybody


    I have a formula


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Which works great, the formula counts all the jobs however I have added another column in L which is for cancelled jobs. I need the formula to subtract or ignore the count if the word 'yes' is typed in column L


    Thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help to amend sumproduct formula to minus depending on entry in another column

    Instead of sumproduct(), take a look at using countifS() instead.

    Maybe something like...

    =countifs('Metering Jobs'!$P$2:$P$20000,B9,'Metering Jobs'!$B$2:$B$20000,Summary!$M$8,new_range,new_criteria)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-16-2012
    Location
    PERTH
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Help to amend sumproduct formula to minus depending on entry in another column

    Thank you but the formula has to work in 2003. Is there a way to do If L = Yes ignore then do sumproduct formula?

    Sorry if I seem a bit basic but I am!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help to amend sumproduct formula to minus depending on entry in another column

    OK, your profile says 2007, hence the countifS().

    Hard to say without any sample data to test with, but maybe this...
    =SUMPRODUCT(('Metering Jobs'!$P$2:$P$20000=B9)*('Metering Jobs'!$B$2:$B$20000=Summary!$M$8)*('Metering Jobs'!$L$2:$L$20000<>"Y"B9))

    Add the IF()

  5. #5
    Registered User
    Join Date
    03-16-2012
    Location
    PERTH
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Help to amend sumproduct formula to minus depending on entry in another column

    Test page.xlsThanks

    Whatever I am doing that does not work. I have uploaded an example file.

    Sheet April 13 Is a list of jobs. Summary is exactly that a summary of jobs. Some of the jobs were not attended 'Cancelled' I do not know how to structure the formula so that my formula counts the jobs i.e PP1 in April = 5 but does not include the cancelled jobs, PP1 in April = 4

    If you can help can you please give me a breakdown of the formula you suggest so I can learn

  6. #6
    Registered User
    Join Date
    03-16-2012
    Location
    PERTH
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Help to amend sumproduct formula to minus depending on entry in another column

    Hi thanks

    I have had help and solved this.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 09-17-2012, 11:43 AM
  2. Need formula for a fixed cell minus last entry on ongoing log
    By Mari C in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  3. [SOLVED] Need formula for a fixed cell minus last entry on ongoing log
    By Kassie in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-06-2005, 07:05 AM
  4. [SOLVED] Need formula for a fixed cell minus last entry on ongoing log
    By Mari C in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  5. Need formula for a fixed cell minus last entry on ongoing log
    By Mari C in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1