Good morning guys,
Having a bugger of a time trying to get these two to work together. Essentially, I'm trying to make a formula that counts between a date range and counts based on the initials of the clerk who performed the action. I can get the two formulas to work in separate cells, but can't find the combo to get them to work together.
Here are the formulas I have:
=SUMPRODUCT(('[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$C:$C>=C1)*('[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$C:$C<=D1))
This adequately returns the amount of total actions processed between the date range specified in C1 and D1. So, C1 says 5/2/2011, D1 says 5/6/2011 and the total number of actions in that date range is 290, which is correct.
But I need to also specify that it meets the criteria of the travel clerk initial, another column on the DTS AUTHORIZATIONS-VOUCHERS page.
=COUNTIF('[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$D:$D,D2)
This adequately returns the number of actions done by the initials specified in cell D2.. but it gives the total actions for the entire worksheet of 1610. I need to break it down further--I need both of these formulas to work together and tell me how many actions the specified initials processed within the specified date range.
Any feedback is greatly appreciated!
Having a bugger of a time trying to get these two to work together. Essentially, I'm trying to make a formula that counts between a date range and counts based on the initials of the clerk who performed the action. I can get the two formulas to work in separate cells, but can't find the combo to get them to work together.
Here are the formulas I have:
=SUMPRODUCT(('[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$C:$C>=C1)*('[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$C:$C<=D1))
This adequately returns the amount of total actions processed between the date range specified in C1 and D1. So, C1 says 5/2/2011, D1 says 5/6/2011 and the total number of actions in that date range is 290, which is correct.
But I need to also specify that it meets the criteria of the travel clerk initial, another column on the DTS AUTHORIZATIONS-VOUCHERS page.
=COUNTIF('[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$D:$D,D2)
This adequately returns the number of actions done by the initials specified in cell D2.. but it gives the total actions for the entire worksheet of 1610. I need to break it down further--I need both of these formulas to work together and tell me how many actions the specified initials processed within the specified date range.
Any feedback is greatly appreciated!