+ Reply to Thread
Results 1 to 7 of 7

If/then statement with variable in logical test and value fields

  1. #1
    Registered User
    Join Date
    06-15-2017
    Location
    Boulder, CO
    MS-Off Ver
    Excel 2016
    Posts
    3

    Question If/then statement with variable in logical test and value fields

    So, I'm trying to automate all the dollar amount fields in the blue table on the right (with titles: group, type, and month), based on the table to the left.

    All rows from the table at the left, under the Group: Advocacy (for example), the dollar amounts from the Cost column in that corresponding row (and all others with Advocacy under Group) should add together into the Advocacy row in the blue table at the right. And so on for each Group (Community, Wedding, Safety, etc.). I'd like to have the same type of thing happen if the cost is associated with Donation/Marketing/Sponsorship in the Type column, and Month, based on Ring Date column.

    I'm very new to if/then statements so could use help. Everything I've found so far are resources to output simple text, but I need to carry over the cost of all rows associated with that group, type, or date into one field.

    Edit: Uploaded a document to manipulate, the current 'desired outcome' is in the cells, just manually added the cost values based on group category.


    Excel If Then Question.JPG
    Attached Files Attached Files
    Last edited by kahayler; 06-15-2017 at 12:39 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,481

    Re: If/then statement with variable in logical test and value fields

    Welcome to the forum!

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: If/then statement with variable in logical test and value fields

    Check out the SUMIF function. Looking at your screenshots, it looks like that would do the trick.

    More on SUMIF here: https://www.techonthenet.com/excel/formulas/sumif.php

    The MONTH column to the far right will need a little more work to match the month name to the dates on the far left.

    We should be able to get you a specific formula once you share an example with us along with the desired outcome of the formulas.

  4. #4
    Registered User
    Join Date
    06-15-2017
    Location
    Boulder, CO
    MS-Off Ver
    Excel 2016
    Posts
    3

    Re: If/then statement with variable in logical test and value fields

    This seems to be working perfectly, thank you so much! I did not think it would be that simple, I appreciate it

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: If/then statement with variable in logical test and value fields

    You're welcome. Glad to help. If that answered your question, please mark this thread as SOLVED.

    By the way, this will give you the MONTH calculation in L3:

    =SUMPRODUCT((MONTH($A$3:$A$110)=MONTH(K3&1))*($C$3:$C$110))
    Last edited by 63falcondude; 06-15-2017 at 12:50 PM.

  6. #6
    Registered User
    Join Date
    06-15-2017
    Location
    Boulder, CO
    MS-Off Ver
    Excel 2016
    Posts
    3

    Re: If/then statement with variable in logical test and value fields

    That was my next update (the month), thank you!

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

    Re: If/then statement with variable in logical test and value fields

    My pleasure. Welcome to the forum.

+ 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] How to inlcude another variable into the logical test of the IF function
    By kiddles13 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-18-2016, 02:30 PM
  2. If statement logical test range cells
    By rhudgins in forum Excel General
    Replies: 4
    Last Post: 12-07-2010, 04:51 PM
  3. Second Logical test in if statement
    By speakers_86 in forum Excel General
    Replies: 3
    Last Post: 12-18-2007, 03:21 PM
  4. [SOLVED] Logical test as a variable in a UDF
    By Dave in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2006, 05:10 PM
  5. [SOLVED] If statement where the logical test is a range that equals a word
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 09-06-2005, 04:05 AM
  6. If statement where the logical test is a range that equals a word
    By Steve o in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  7. If statement where the logical test is a range that equals a word
    By Steve o in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-27-2005, 10:05 AM

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