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
Bookmarks