+ Reply to Thread
Results 1 to 13 of 13

IF statement for a range of data

  1. #1
    Registered User
    Join Date
    05-14-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    IF statement for a range of data

    Hey guys,

    I am working on a spreadsheet where I have multiple classes in b5:b22. I want to build a formula that will look at this range and based on a certain criteria (such as choosing Class A) it will look over at the next column (c5:c22) and sum up the number of students that fall under "Class A." I am not sure if this will make things more clear, but the classes repeat several times in b5:b22 (Class A is most prominent). I should mention that there are a certain amount of students in each class (so in each cell from c5:c22). For example, Class A has 24, 32, 12, 42. Class B has 4, 12, 9, etc. I am trying to get the sum of all of the Class A's.

    So far, I have tried to use =IF(COUNTIF(B5:B22,"Class A"),C5:C22,0) but I am aware that this will not work since it does not make sense, specifically with the value if true statement.

    Thank you for your help!
    Last edited by waterdrinker; 05-14-2014 at 02:37 PM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: IF statement for a range of data

    Try this one (untested)

    =COUNTIF(B5:B22,"Class A",C5:C22)
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    05-14-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: IF statement for a range of data

    Doesn't work - too many arguments. I should mention that there are a certain amount of students in each class (so in each cell from c5:c22). For example, Class A has 24, 32, 12, 42. Class B has 4, 12, 9, etc. I am trying to get the sum of all of the Class A's.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: IF statement for a range of data

    Please show us your excel file, without confidential information.

  5. #5
    Registered User
    Join Date
    05-14-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: IF statement for a range of data

    There you go. Let me know if you need anything else, I removed quite a bit.

    Classroom Example.xlsx

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: IF statement for a range of data

    with an pivot table.

    see the attached file.

  7. #7
    Registered User
    Join Date
    05-14-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: IF statement for a range of data

    I will try recreating the Pivot table on my spreadsheet. I'll let you know if it works out. Thanks!

  8. #8
    Registered User
    Join Date
    05-14-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: IF statement for a range of data

    Quote Originally Posted by oeldere View Post
    with an pivot table.

    see the attached file.
    Works perfectly! Thanks for your help!

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: IF statement for a range of data

    See post #2 (tested)

    This one does works (as already suggested).

    Please Login or Register  to view this content.
    Thanks for the reply.

    Glad I could help.

  10. #10
    Registered User
    Join Date
    05-14-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: IF statement for a range of data

    I tried it again and got too many arguments.

    Also when I created the pivot table on my spreadsheet, it split up one of my classes into 2. So for example, it was class a --> 40, class b -->23, class c --> 12, class c --> 10

    Any idea on why it is doing this?

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: IF statement for a range of data

    Quote Originally Posted by oeldere View Post
    See post #2 (tested)

    This one does works (as already suggested).

    Please Login or Register  to view this content.
    COUNTIF() takes 1 range and 1 criteria, maybe you meant countifS()?

    Edit: OK after reading the question (properly), I think you need SUMIF()

    =sumif(B5:B22,"Class A",C5:C22)
    Last edited by FDibbins; 05-14-2014 at 04:05 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  12. #12
    Registered User
    Join Date
    05-14-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: IF statement for a range of data

    Quote Originally Posted by FDibbins View Post
    COUNTIF() takes 1 range and 1 criteria, maybe you meant countifS()?

    Edit: OK after reading the question (properly), I think you need SUMIF()

    =sumif(B5:B22,"Class A",C5:C22)
    That's what I was looking for! Thank you very much.

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: IF statement for a range of data

    @Fdibbins

    great you sorted it out (for me)

    of course it needed to be sumif instead of my countif.

+ 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. If statement with a range and true/false statement
    By aarleblanc in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-20-2012, 06:34 PM
  2. If statement based on cell data, if falls in range.
    By finch82 in forum Excel General
    Replies: 3
    Last Post: 10-27-2011, 04:11 PM
  3. If statement with a range.
    By Mummra in forum Excel General
    Replies: 3
    Last Post: 10-13-2011, 10:29 AM
  4. limit textbox data to number range with IF statement
    By randyvann in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-11-2008, 04:15 PM
  5. IF statement in Bar Chart Data Range
    By kollerrk in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-09-2008, 09:37 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