+ Reply to Thread
Results 1 to 3 of 3

Sumif/Match formula that makes a space blank

  1. #1
    Registered User
    Join Date
    03-15-2014
    Location
    Nashville, TN
    MS-Off Ver
    365
    Posts
    33

    Sumif/Match formula that makes a space blank

    Guys,

    This is a tough one. I’m trying to find a formula that when sales data is dropped into the sheet titled “sales,” it finds the customer number (which I have shown in red) on the first tab (titled “Leads”) in column A and if the customer number matches, then the cell in column G (“Total Price” on the “Leads Tab” colored in green) changes to “0” in that particular row.

    I’ve been tinkering with “Match” and “Sumif” formulas, but not having any luck.

    For example, if it’s correct in this case, cell G3 (colored orange) will turn to “0” since the customer ID’s in that row from the “sales” tab match up.

    The names have been changed for privacy.

    Appreciate the help so much.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    02-05-2015
    Location
    Edmonton, Alberta
    MS-Off Ver
    2013
    Posts
    70

    Re: Sumif/Match formula that makes a space blank

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



    as a project manager i would create a new column in H and call it Outstanding Leads balance and then paste this formula in H2 and drag down. this will deduct the sales from the sales pipeline.

    you could also use this to zero out like you asked

    you would still need to past this in H2 and drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Sumif/Match formula that makes a space blank

    You can't enter a value and a formula in the same cell so a helper column is necessary. I made one called Balance in column H of Leads and entered this formula in H2 and filled down. If the customer number doesn't appear in the sales worksheet, the balance is the same as in column G (you can have what you want just change the "" with what you would like)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    E
    F
    G
    H
    1
    patron_specialist
    ps_phone
    Total_Price
    Balance
    2
    Jim Bik
    $ 3,704.00
    $ 3,704.00
    3
    Henry Kally
    $ 558.00
    4
    Tim Donald
    $ 1,176.00
    $ 1,176.00
    5
    Dennis Mitchum 615.687.6471
    $ 558.00
    $ 558.00
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ 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. Index/ Match Formula, How to return blank cell as a blank not 0
    By MDResearcher in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-24-2016, 08:40 PM
  2. Replies: 3
    Last Post: 03-26-2014, 10:45 AM
  3. Extract data that makes up the result of a SUMIF function
    By andrewdoes in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-13-2013, 12:56 PM
  4. [SOLVED] fix formula for a 0 if blank space
    By sacrous in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-28-2012, 10:03 AM
  5. Scaling Makes Spreadsheet Blank?
    By stormie49548 in forum Excel General
    Replies: 0
    Last Post: 07-19-2012, 07:53 PM
  6. Custom Formatting makes formula not recognize cell as blank
    By williamthomp in forum Excel General
    Replies: 6
    Last Post: 04-13-2012, 03:33 PM
  7. [SOLVED] I want a blank space on a worksheet when the question is blank?
    By Patrizia in forum Excel General
    Replies: 2
    Last Post: 06-22-2005, 08:05 PM
  8. Getting any formula to stop at the next blank space
    By ajpowers in forum Excel General
    Replies: 0
    Last Post: 06-09-2005, 04:41 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