RyanRothchild
New Member
- Joined
- May 1, 2014
- Messages
- 1
For your consideration and appreciated assistance:
In MS Excel 2010
In my workbook, I have multiple named ranges containing the Identification codes for sub-organizations (companies) within my units (battalions). Each named range contains five to seven company ID codes per battalion. I also have multiple sheets containing different queries for records of activity within those organizations. One such page returns the status of schedules submitted for approval by each company. Column D contains the time period for the schedule by listing the week number in the fiscal year. Column F contains the status of the schedule (approved, disapproved, pending approval, etc.) Column G contains the Identificaiton Code for the unit. These ID codes have been grouped into named ranges that represent their parent batallion organization. For example, the GUNNER batallion contains ID Codes UA0, UB0, UT0, and RG0 representing the four companies in that batallion. Each company is required to submit a schedule for approval for each week (time period).
On the first page, I have generated a roll-up that should calculate the total number of approved schedules for each battalion in a given time period.
I need a formula that will count all the records in the worksheet only if the value in column G falls in a certain battalion named range, and the value in column D is the specifid time period, and the value in column F is "approved."
So far, I have come up with this, and it doesn't work:
=COUNTIFS(COTNGSCHD!$G:$G,GUNNER,COTNGSCHD!$D:$D,$B4,COTNGSCHD!$F:$F,"APPROVED")
where COTNGSCHD is the sheet name that contains the data, GUNNER is the named range for the Battalion, and $B4 is the cell on the rollup sheet in which I specify the time period.
What am I doing wrong and how can I fix my rollup formula.
In MS Excel 2010
In my workbook, I have multiple named ranges containing the Identification codes for sub-organizations (companies) within my units (battalions). Each named range contains five to seven company ID codes per battalion. I also have multiple sheets containing different queries for records of activity within those organizations. One such page returns the status of schedules submitted for approval by each company. Column D contains the time period for the schedule by listing the week number in the fiscal year. Column F contains the status of the schedule (approved, disapproved, pending approval, etc.) Column G contains the Identificaiton Code for the unit. These ID codes have been grouped into named ranges that represent their parent batallion organization. For example, the GUNNER batallion contains ID Codes UA0, UB0, UT0, and RG0 representing the four companies in that batallion. Each company is required to submit a schedule for approval for each week (time period).
On the first page, I have generated a roll-up that should calculate the total number of approved schedules for each battalion in a given time period.
I need a formula that will count all the records in the worksheet only if the value in column G falls in a certain battalion named range, and the value in column D is the specifid time period, and the value in column F is "approved."
So far, I have come up with this, and it doesn't work:
=COUNTIFS(COTNGSCHD!$G:$G,GUNNER,COTNGSCHD!$D:$D,$B4,COTNGSCHD!$F:$F,"APPROVED")
where COTNGSCHD is the sheet name that contains the data, GUNNER is the named range for the Battalion, and $B4 is the cell on the rollup sheet in which I specify the time period.
What am I doing wrong and how can I fix my rollup formula.
