+ Reply to Thread
Results 1 to 13 of 13

Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

  1. #1
    Registered User
    Join Date
    02-26-2019
    Location
    USA
    MS-Off Ver
    Microsoft 365
    Posts
    42

    Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

    This problem has likely been asked and answered but it's hard to word it correctly for a search to find.

    I need a formula that does- If Column L on Sheet1 says "X", add(find the sum) of Column K on Sheet1.

    Thank you!

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

    Try this:

    =SUMIF(L:L,"X",K:K)

    If this formula is on another sheet, you will have to add Sheet1! before the column references.

  3. #3
    Registered User
    Join Date
    02-26-2019
    Location
    USA
    MS-Off Ver
    Microsoft 365
    Posts
    42

    Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

    SumIf, that makes sense.
    It's not working for me though.... Any idea why?
    Here is what I am using:
    =SUMIF('Certified Tracker'!F:F,"Bloomfield",'Certified Tracker'!K:K)

    I have plenty of data on Sheet1, 'Certified Tracker', that has Bloomfield in column F and numbers in column K, but the formula is returning '0'.

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

    I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

    if Bloomfield is part of a text string or has a leading or trailing space then the =SUMIF('Certified Tracker'!F:F,"Bloomfield",'Certified Tracker'!K:K) will not work.
    you might need to change it to =SUMIF('Certified Tracker'!F:F,"*Bloomfield*",'Certified Tracker'!K:K) to pick it up as part of a string.

    AND, make sure your numbers in col K aren't text.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  6. #6
    Registered User
    Join Date
    02-26-2019
    Location
    USA
    MS-Off Ver
    Microsoft 365
    Posts
    42

    Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

    See attached. On Sheet2, 'Blmfld', I want cell C12 to have a formula to add all the 'projected units' together for region 'Bloomfield'.

    Thank you!
    Attached Files Attached Files

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

    The numbers in column K are being seen as Text.

    To fix this, highlight column K > Data > Text to Columns > Finish.

    Then the SUMIF formula should work.

  8. #8
    Registered User
    Join Date
    02-26-2019
    Location
    USA
    MS-Off Ver
    Microsoft 365
    Posts
    42

    Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

    Fabulous thank you.
    I also see Sambo Kid said this above, not sure if it was edited and I saw it too quickly or if I missed it but either way thank you both

  9. #9
    Registered User
    Join Date
    02-26-2019
    Location
    USA
    MS-Off Ver
    Microsoft 365
    Posts
    42

    Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

    Hello again.

    Changing my numbers to be stored as numbers instead of text worked on the Sample document I provided, but for some reason I cannot get it to work on my actual document. Please see attached, another Sample replicating the issue.

    The numbers in row K are being formatted as numbers, so why isn't the formula in 'Blmfld Tier 2' cell C12 working?

    Thank you!
    Attached Files Attached Files

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

    you still have text values in column K of the certified tracker.
    change your formula in K to this... =IF(L35=1, 0,IF(L35=2,0,IF(L35=3,3,IF(L35=4,3,IF(L35=5,4,IF(L35=6,4,IF(L35=7,4,IF(L35=8,4,IF(L35=9,6,IF(L35=10,6,IF(L35>10,6))))))))))) by getting rid of the quotes around the numbers.

  11. #11
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

    actually, you could change your if formula to this to shorten it... =IF(OR(L42=1,L42=2),0,IF(OR(L42=3,L42=4),3,IF(OR(L42=5,L42=6,L42=7,L42=8),4,IF(OR(L42=9,L42>=10),6))))
    there are likely other ways too like this... =LOOKUP(L42,{1,3,5,9},{0,3,4,6})

  12. #12
    Registered User
    Join Date
    02-26-2019
    Location
    USA
    MS-Off Ver
    Microsoft 365
    Posts
    42

    Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

    AH! The quotes around the numbers are the problem! Also thanks so much for showing me that way to shorten this!! I figured there must be some way to do exactly that but didn't know how and typing it all out worked just fine, too.

    Thank you!!!

  13. #13
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

    You're welcome! Glad I could help. You'll learn this stuff as you use excel more and refer to this site - I know I did.
    AND thank you for the rep!

+ 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. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  2. [SOLVED] Link excel file to workbook "=IF(ISBLANK('Sheet1!A1),"",'Sheet1!A1) " is this reliable?
    By bala04msw in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-29-2016, 05:39 AM
  3. Copy Values from Sheet1 to Sheet2 only if column I has a "X" in the line
    By theglitch in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-29-2015, 06:46 PM
  4. [SOLVED] If there is any text in column "A$" on "sheet1" then move cell to column "A$" on "sheet2"
    By ckgeary in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-27-2013, 08:28 PM
  5. Copy column from sheet2 if specific cell in sheet1 is "QLD"
    By matrixpom in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-09-2012, 04:57 PM
  6. [SOLVED] comparing data in column [sheet1] with data in column [sheet2] and execute "yes" or "no"
    By dragon_m0nsta in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-14-2012, 03:27 AM
  7. Replies: 2
    Last Post: 05-05-2008, 04:51 PM

Tags for this Thread

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