J
Jeff
I store data on one tab and write a report template on another. The report
template allows a selection of criteria, usually a date, and the appropriate
data is pulled from the data tab.
My data tab looks like this:
A B C D E F G
1 Code Title Dept Division Date 1 Date 2 Date 3 etc
2 001 CFO Acct Admin 1 1 1
3 002 Staff Acct Admin 4.5 4.7 4.4
4 003 Dir HR Admin 1 1 1
5 004 Clerk Purch Admin 3.1 3.1 3.0
etc wth 300 or so row and 26 or so columns.
The numbers below the dates are FTES (Full Time Equivalents a HR measure)
On my report tab, I have the depts listed in column a. I have a changable
date in B1 that is to correspond with one of the dates on the data tab.
I need a flexible formula to say 'add up all the FTEs for a certain Dept on
a certain date.'
1. sumif will allow me to pick a dept, but then I must hard code my column
to select from: sumif(Data!C:C,A2,E:E) for date one. [Data!C:C is my column
of depts on the data tab, A2 is my row dept name like "Acct". E:E would
return data for date 1]
2. sumif will allow me to pick a date-horizontal, but then I must hard code
my row to select from: sumif(Data!1:1,B1,3:3) for staff accountant. [Data!1:1
is my row of dates on the data tab, B1 is the user input date. 3:3 would
return data for Staff in Acct]
What I really wanted excel to do was sumifs(Data!C:C,"Admin",Data!1:1,'Date
1',E2:Z300) ie. sum up anything that intersected these two conditions, but
it doesn't like that. I understand that I could do a lot of hard coding, but
I want it as flexible as possible. I am creating it and then turning it over
to a data entry clerk to maintain.
With all that, my apologies for the length and thanks ahead of time!
template allows a selection of criteria, usually a date, and the appropriate
data is pulled from the data tab.
My data tab looks like this:
A B C D E F G
1 Code Title Dept Division Date 1 Date 2 Date 3 etc
2 001 CFO Acct Admin 1 1 1
3 002 Staff Acct Admin 4.5 4.7 4.4
4 003 Dir HR Admin 1 1 1
5 004 Clerk Purch Admin 3.1 3.1 3.0
etc wth 300 or so row and 26 or so columns.
The numbers below the dates are FTES (Full Time Equivalents a HR measure)
On my report tab, I have the depts listed in column a. I have a changable
date in B1 that is to correspond with one of the dates on the data tab.
I need a flexible formula to say 'add up all the FTEs for a certain Dept on
a certain date.'
1. sumif will allow me to pick a dept, but then I must hard code my column
to select from: sumif(Data!C:C,A2,E:E) for date one. [Data!C:C is my column
of depts on the data tab, A2 is my row dept name like "Acct". E:E would
return data for date 1]
2. sumif will allow me to pick a date-horizontal, but then I must hard code
my row to select from: sumif(Data!1:1,B1,3:3) for staff accountant. [Data!1:1
is my row of dates on the data tab, B1 is the user input date. 3:3 would
return data for Staff in Acct]
What I really wanted excel to do was sumifs(Data!C:C,"Admin",Data!1:1,'Date
1',E2:Z300) ie. sum up anything that intersected these two conditions, but
it doesn't like that. I understand that I could do a lot of hard coding, but
I want it as flexible as possible. I am creating it and then turning it over
to a data entry clerk to maintain.
With all that, my apologies for the length and thanks ahead of time!