+ Reply to Thread
Results 1 to 8 of 8

Two Formulas in one cell

  1. #1
    Registered User
    Join Date
    03-19-2021
    Location
    Spain
    MS-Off Ver
    365
    Posts
    56

    Two Formulas in one cell

    I have a problem I have been trying to sort but with no success.
    On the sheet that is attached I have a SUBPRODUCT formula that is in row 18 that produces 2 if correct in row 12
    There is another SUBPRODUCT FORMULA IN ROW 20 that produces 5 if correct,
    What I want is if the formulas in row 20 produce 5 then the rows 18 produce 0 instead of 2,
    I have made it sound more complicated than it is .but the attachment will help

    Thank you for any input John
    Attached Files Attached Files
    Last edited by John Henworth; 03-20-2021 at 07:02 AM.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Two Formulas in one cell

    Hi John and welcome

    You can 'nest' the SUMPRODUCT inside an IF that checks the value in row 20 first. So the below formula would go in C18 and be copied across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The red part is your current formula in C18.

    That will show 0 in row 18 if row 20 contains 5 and will calculate the SUMPRODUCT if not.

    Does that help?

    BSB

  3. #3
    Registered User
    Join Date
    03-19-2021
    Location
    Spain
    MS-Off Ver
    365
    Posts
    56

    Re: Two Formulas in one cell

    Thank you so much for replying.
    I think your formula is nearly what I want.
    I still want Row 18 to show 2 if row 20 is incorrect
    On my attachment there is a second page so you can work on please

    Kind regards John
    I love your name

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Two Formulas in one cell

    I've amended the formulas on the "Super 6" sheet in the attached version.
    See if that does what you need.

    BSB
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-19-2021
    Location
    Spain
    MS-Off Ver
    365
    Posts
    56

    Re: Two Formulas in one cell

    Thanks so much
    That is absolutely great,
    Dont know how you do it, I need more lessons John

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Two Formulas in one cell

    It's only easy when you know how, John!

    You can learn all you need to know by asking questions on the forum. We're here to help you learn.

    BSB

  7. #7
    Registered User
    Join Date
    03-19-2021
    Location
    Spain
    MS-Off Ver
    365
    Posts
    56

    Re: Two Formulas in one cell

    Hello BSB, I am new to this forum and was surprised how quick you answered my problem, I did not explain it very well but you sorted it for me straight away. I will try and remember all that you sorted out.
    How did you learn to be so good?
    Thanks once again John

  8. #8
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Two Formulas in one cell

    Quite simply, necessity and years of practice. I found myself with a job that meant I was using Excel all the time. I'm often a little lazy so spent time learning a few little tricks in Excel to make my worklife easier. Litte tricks lead to more complicated tricks and now after two decades of using Excel the learned tricks mean I write code to automate repetetive tasks.

    Your original request here was quite a simple fix, but as I said earlier, only easy when you know how. I've been running football competitions like this for years so I understood what you needed and how to help.

    It's actually a very simple program to learn to use once you understand a few of the basics. Start small and ask questions when you get stuck and it will all fall into place quite quickly.

    BSB

+ 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: 4
    Last Post: 10-19-2016, 02:45 PM
  2. Using Cell references in file paths for formulas to create dynamic formulas
    By MichaelStokesJr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-04-2013, 11:49 AM
  3. View cell info instead of formulas / Evaluate formulas
    By whitetea in forum Excel General
    Replies: 2
    Last Post: 05-20-2012, 10:08 AM
  4. Excel 2007 : Future Date Formulas and Adjacent Cell Formulas
    By thetaylor13 in forum Excel General
    Replies: 4
    Last Post: 02-09-2011, 04:33 PM
  5. Multiple formulas in selected cell without damaging previous formulas.
    By excel5111987 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-05-2011, 06:15 AM
  6. Replies: 5
    Last Post: 05-05-2008, 02:22 PM
  7. Formulas in a cell don't work as formulas.
    By Lyle McElhaney in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-01-2007, 05:04 AM

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