+ Reply to Thread
Results 1 to 6 of 6

Find last row and count multiple criteria using last row (bottom up) for results

  1. #1
    Registered User
    Join Date
    01-25-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    19

    Find last row and count multiple criteria using last row (bottom up) for results

    I'm not up on VBA as of yet, so I'm looking for a formula that will generate my results for right now.

    Need formula where I can set my search within a set RANGE (A1:A5000, BA1:BA5000) (DID THIS SO THAT I DONíT HAVE TO MANUALLY CHANGE FORMULA EACH TIME REPORT IS RUNÖ ANTICIPATING FUTURE GROWTH) and fills in my Template form

    SCENERIO: ADHOC report were the columns remain constant and COLUMN A will always have data present.
    1) Use the range, but FIND the LAST ROW within the range to determine end of record dependent on Column A (always has data).
    EX: RANGE SET AS (A1:A5000, BA1:BA5000), BUT the last row (last student record) in my spreadsheet is 3935 (currently) not 5000 (future growth).

    2) Once LAST ROW identified within RANGE, I need to COUNT Multiply criteria from Column AZ and BA

    To add up the multiple criteria for Ethnicity NHS (stands for Non-Hispanic) + Race Asian (AN) + Race Blank ('=') = Asian count. This formula also works, but would like to insert a condition that will find the LAST ROW to ensure the count stops
    =SUM(COUNTIFS('Census 2012'!$AZ$2:$AZ$5000,"NHS",'Census 2012'!$BA$2:$BA$5000,"AN")+ COUNTIFS('Census 2012'!$BA$2:BA$5000,"AN",'Census 2012'!$AZ$2:$AZ$5000,"="))


    To add up the multiple criteria for Ethnicity HIS (stand for Hispanic) + Race Asian (AN) = Hispanic count. This formula also works, again condition to find LAST ROW
    =SUM(COUNTIFS('Census 2012'!BA2:BA5000,{"AN","AS","BL","HP","WH","="}, 'Census 2012'!AZ2:AZ5000,"HIS"))


    3) To add up the multiply criteria for Ethnicity NHS (stands for Non-Hispanic) is "BLANK" + Race is "BLANK" = Unknown count
    Because students donít always answer all the required data on their online application, these fields appear as BLANK CELLS when data is retrieved in the ADHOC report. I used the following formula for the time being and it works, but I would have to verify last record and change the End of Record range AZ:_____ each time to match. If I didnít use this current formula, it would keep counting until record BA5000 throwing off my actual count. This field is my Unknown student population.
    =COUNTIFS('Census 2012'!AZ2:AZ3935,"",'Census 2012'!BA2:BA3935,"")


    Sorry for the length of the inquiry, but I wanted to be as detailed as possible to explain my situation and alleviate back and forth questions. I'm on a deadline to create our FACTBOOK and I refuse to manually look this data up by creating multiple Pivot tables to get my data. Do you have any suggestions (a formula) that will read LAST ROW and return a count using my criteria?

    Your expertise is most appreciated!!! Thx in advance. Cris
    Attached Files Attached Files

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

    Re: Find last row and count multiple criteria using last row (bottom up) for results

    With an table

    => excel 2007 => insert => table.

    And after that a pivot table.

    => excel 2007 => insert => pivot table.

    See the attached file.
    Attached Files Attached Files
    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
    01-25-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Find last row and count multiple criteria using last row (bottom up) for results

    Thx oeldere, but I'm not looking for Pivot tables... I use them already to get such data.

    I'm looking for a formula that will go to the LAST ROW based on COLUMN A and use the LAST ROW as my "End of Record" identifier and use the LAST ROW found and go COLUMN BA to calculate from LAST ROW bottom/up and count the number of "Blank cells" to give me my UNKNOWN students. I as stated, my COUNTIFS work, but I must manually adjust the formula and insert the LAST ROW from a visual lookup and change the range. This is what I'm trying to alliviate.

    The concept like what INDEX will do, but I'm not wanting to know the LAST ROW NUMBER, just want to use the idea of finding the LAST ROW and possibly do a COUNTIF for COLUMN BA. How to combine the two???

    =INDEX(Sheet1!A2:A5000,MATCH(1E+100,Sheet1!A2:A5000))

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,463

    Re: Find last row and count multiple criteria using last row (bottom up) for results

    May the OFFSET work?:

    To limit the range A2:A5000 to the last row, try

    =OFFSET(A2,,,,COUNTA(A2:A5000))

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

    Re: Find last row and count multiple criteria using last row (bottom up) for results

    @cvercrus

    1) did you read my post well enough

    2) and did you made an table of the data?

  6. #6
    Registered User
    Join Date
    01-25-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    19

    SOLVED - Find last row and count multiple criteria using last row (bottom up) for results

    Thx all that replied. I found a fix that extracts the data that I was looking for. Although Column A always had data present, I used Column G that also always has data present and the two groups (Graduates and undergraduates) are used constantly. Therefore, I just separated the two groups for data analysis and came up with this formula.

    By choosing Column G, I was still able to set my range to 5000 since the formula counts the number of "Undergraduates" in the formula below who claimed NHS (Non-hispanic) but did not claim race "blank cell" OR who were Undergraduates, did not claim Ethnicity "blank cell" and did not claim race "blank cell".

    Iím sure there are other ways, but this gives me exactly what I was needing and I can stills set my range for 5000 to support increase in enrollment for the future.

    Thx again!!! Cris

    =SUM(COUNTIFS('Census 2011'!$G$2:$G$5000,"UG",'Census 2011'!$AZ$2:$AZ$5000,"NHS",'Census 2011'!$BA$2:$BA$5000,"")+COUNTIFS('Census 2011'!$G$2:$G$5000,"UG",'Census 2011'!$AZ$2:$AZ$5000,"",'Census 2011'!$BA$2:$BA$5000,""))
    Last edited by cvercrus; 02-04-2013 at 12:35 PM. Reason: SOLVED

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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