+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : Trouble with Countifs

  1. #1
    Registered User
    Join Date
    03-09-2012
    Location
    Belgium
    MS-Off Ver
    2007
    Posts
    14

    Trouble with Countifs

    I am attempting to give credit to a person if they worked on a report. 1 point per report worked on. I have tabs for the reports per quarter and a talley tab. The report sheet for the 1st quarter (FY 12 - 1st Q), E Column has complete or incomplete, G Column has their student number in it, but can have more than one student number in it. (I do not want to merge multiple cells and split the two numbers apart as I am already doing that somewhere else in the sheet). On the talley sheet, (FY 12 - Stu Tot) I have their names, (Column A), next to their student number (Column B). Is it possibe to get Column C - (1st Quarter) of (FY 12 - Stu Tot) to talley the total number of times it sees the student number. I have gotten this far but don't want to have to keep retyping the student numbers:
    =countifs('FY 12 - 1st Q!G3:G1000, "*1458*", 'FY 12 - 1st Q!E3:E1000,"complete")
    Since 1458 is in Column B of the Stud Tot sheet is there a way to make that work without retyping every student number.

    I abbreviated for shortness (i.e. student numbers are much longer than 4 digits)
    Last edited by jayres14; 03-09-2012 at 09:07 AM. Reason: Solved

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Trouble with Countifs

    You can reference the student number in a cell like so:

    =countifs('FY 12 - 1st Q!$G$3:$G$1000, "*" & B2 & "*", 'FY 12 - 1st Q!$E$3:$E$1000,"complete")

    Where B2 contains the student number, and you need to make the ranges absolute with the $ signs in order to "freeze" them. Then you can copy down the formula.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Trouble with Countifs

    Try this version in C2 copied down

    =COUNTIFS('FY 12 - 1st Q!G$3:G$1000, "*"&B2&"*", 'FY 12 - 1st Q!E$3:E$1000,"complete")

    I'm assuming that all student numbers are 4 digits are they, no possibility of having one student number as a "substring" of another?
    Audere est facere

  4. #4
    Registered User
    Join Date
    03-09-2012
    Location
    Belgium
    MS-Off Ver
    2007
    Posts
    14

    Thumbs up Re: Trouble with Countifs

    Quote Originally Posted by NBVC View Post
    You can reference the student number in a cell like so:

    =countifs('FY 12 - 1st Q!$G$3:$G$1000, "*" & B2 & "*", 'FY 12 - 1st Q!$E$3:$E$1000,"complete")

    Where B2 contains the student number, and you need to make the ranges absolute with the $ signs in order to "freeze" them. Then you can copy down the formula.
    Thank you very much! This worked perfectly, and reminded me of the whole "freeze" aspect.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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