Sumifs (I think)

[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!

 

[Jacob Skaria]

In report tab with cell a2 = department name

=SUMPRODUCT((Data!C2:C300=A2)*(Data!E2:Z300))

If this post helps click Yes

 

[Jeff]

This returns the sum of all my dept FTEs for all of my dates (all my
columns). I need to have a selection criteria that picks only one column E
to Z.

In report tab with cell a2 = department name

=SUMPRODUCT((Data!C2:C300=A2)*(Data!E2:Z300))

If this post helps click Yes
---------------
Jacob Skaria

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!

 

[Jacob Skaria]

With Dept in A2 and Date in B2 try

=SUMPRODUCT((Data!C2:C300=A2)*(Data!E1:Z1=B2)*(Data!E2:Z300))

If this post helps click Yes
---------------
Jacob Skaria

This returns the sum of all my dept FTEs for all of my dates (all my
columns). I need to have a selection criteria that picks only one column E
to Z.

In report tab with cell a2 = department name

=SUMPRODUCT((Data!C2:C300=A2)*(Data!E2:Z300))

If this post helps click Yes
---------------
Jacob Skaria

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!

 

[Jeff]

I am getting reasonable results. I have to digest this sumproduct formula
(acts more like a sumifs to me). Thanks for the great help!!!!

With Dept in A2 and Date in B2 try

=SUMPRODUCT((Data!C2:C300=A2)*(Data!E1:Z1=B2)*(Data!E2:Z300))

If this post helps click Yes
---------------
Jacob Skaria

This returns the sum of all my dept FTEs for all of my dates (all my
columns). I need to have a selection criteria that picks only one column E
to Z.

In report tab with cell a2 = department name

=SUMPRODUCT((Data!C2:C300=A2)*(Data!E2:Z300))

If this post helps click Yes
---------------
Jacob Skaria


:

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!

 

[Jeff]

Fasicnatingly brillant. It didn't strike me at first, but the logical tests
of equal to produces a 1 when true and a 0 when false. Hence anything that
meets your criteria is times 1 and times zero if false.

Thanks for the trick!!!

With Dept in A2 and Date in B2 try

=SUMPRODUCT((Data!C2:C300=A2)*(Data!E1:Z1=B2)*(Data!E2:Z300))

If this post helps click Yes
---------------
Jacob Skaria

This returns the sum of all my dept FTEs for all of my dates (all my
columns). I need to have a selection criteria that picks only one column E
to Z.

In report tab with cell a2 = department name

=SUMPRODUCT((Data!C2:C300=A2)*(Data!E2:Z300))

If this post helps click Yes
---------------
Jacob Skaria


:

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!