+ Reply to Thread
Results 1 to 6 of 6

IF Statement Help

  1. #1
    Registered User
    Join Date
    12-08-2016
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    4

    IF Statement Help

    HELLO!

    I am somewhere between beginner and intermediate in Excel. I'm working on creating an if statement but am not quite sure how to get it just right, as it's a little more complicated than those I've done before.

    I have a column containing values such as 24A-24E, and another column containing numerical values. What I am looking to do is if the value in B7:B160 is 24A, I want it to add the number in AV7:AV160 to a different cell that will hold a running total. IF the value in B7:B160 is 24B, add that number to the cell for 24B, etc.

    I need to know the total of any line that has 24A as the account, and the total of any line that has 24B as the account, etc. Basically, if B7 is 24A, put the number in AV7 into a cell that holds a sum for each item that is 24A. Not just a count; I need the sum of those cells.

    Hopefully that makes sense. TIA for any help!

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,001

    Re: IF Statement Help

    sounds like a SUMIF() or SUMIFS()

    but not 100% certain on what you need to so

    if B7 is 24A then you want "different Cell" to contain the value from AV7
    if B8 is 24B then you want "different Cell" to contain the value from AV8
    or do you want this different Cell to be the sum of AV7 and AV8 ,assuming the "different cell" is the same cell reference in all cases

    =SUMIF( B7:B160 , "24A" , AV7:AV160 )
    will give the total for all 24A
    =SUMIF( B7:B160 , "24B" , AV7:AV160 )
    will give the total for all 24B
    =SUMIF( B7:B160 , "24A" , AV7:AV160 )+SUMIF( B7:B160 , "24B! , AV7:AV160 )
    will add the two together

    you want to do for 24A to 24E
    so you would need the 5 "SUMIF()" added together


    Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "Choose File" (top Left corner).
    Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Last edited by etaf; 12-08-2016 at 10:13 AM.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: IF Statement Help

    You mention you don't want the count you want the sum of the cells, but the values of the cells are 24 and a letter, so what are you looking to sum?

    If there is another column you can use =SUMIF(A:A,"24A",B:B)

    Column A Column B
    24B 20
    24B 50
    24A 12

  4. #4
    Registered User
    Join Date
    12-08-2016
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    4

    Re: IF Statement Help

    Quote Originally Posted by etaf View Post
    sounds like a SUMIF() or SUMIFS()

    but not 100% certain on what you need to so

    if B7 is 24A then you want "different Cell" to contain the value from AV7
    if B8 is 24B then you want "different Cell" to contain the value from AV8
    or do you want this different Cell to be the sum of AV7 and AV8 ,assuming the "different cell" is the same cell reference in all cases

    =SUMIF( B7:B160 , "24A" , AV7:AV160 )
    will give the total for all 24A
    =SUMIF( B7:B160 , "24B" , AV7:AV160 )
    will give the total for all 24B
    =SUMIF( B7:B160 , "24A" , AV7:AV160 )+SUMIF( B7:B160 , "24B! , AV7:AV160 )
    will add the two together

    you want to do for 24A to 24E
    so you would need the 5 "SUMIF()" added together


    Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "Choose File" (top Left corner).
    Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    The =SUMIF worked perfectly! THANK YOU! I kept trying =IF, and then SUM later on in the formula and it just wasn't working of course.

  5. #5
    Registered User
    Join Date
    12-08-2016
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    4

    Re: IF Statement Help

    Quote Originally Posted by etaf View Post
    sounds like a SUMIF() or SUMIFS()

    but not 100% certain on what you need to so

    if B7 is 24A then you want "different Cell" to contain the value from AV7
    if B8 is 24B then you want "different Cell" to contain the value from AV8
    or do you want this different Cell to be the sum of AV7 and AV8 ,assuming the "different cell" is the same cell reference in all cases

    =SUMIF( B7:B160 , "24A" , AV7:AV160 )
    will give the total for all 24A
    =SUMIF( B7:B160 , "24B" , AV7:AV160 )
    will give the total for all 24B
    =SUMIF( B7:B160 , "24A" , AV7:AV160 )+SUMIF( B7:B160 , "24B! , AV7:AV160 )
    will add the two together

    you want to do for 24A to 24E
    so you would need the 5 "SUMIF()" added together


    Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "Choose File" (top Left corner).
    Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    The =SUMIF worked perfectly! THANK YOU! I kept trying =IF, and then SUM later on in the formula and it just wasn't working of course.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,001

    Re: IF Statement Help

    you are welcome
    if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here

+ 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: 2
    Last Post: 07-09-2015, 04:25 PM
  2. [SOLVED] If statement to select data - nested statement - assistance
    By petitesouris in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-29-2015, 09:55 PM
  3. compile error expected line number statement end statement
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2014, 10:12 AM
  4. VBA Compile Error : line number or label or statement or end of statement
    By excellearner121 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-04-2013, 06:41 PM
  5. Replies: 4
    Last Post: 06-01-2012, 10:05 AM
  6. Replies: 4
    Last Post: 05-16-2012, 05:33 PM
  7. [SOLVED] Utilize a Select Case Statement in Target Intersect Statement
    By max57 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-29-2009, 08:55 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