Sumifs (I think)

  • Thread starter Thread starter Jeff
  • Start date Start date
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!
 
In report tab with cell a2 = department name

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

If this post helps click Yes
 
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.

Jacob Skaria said:
In report tab with cell a2 = department name

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

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


Jeff said:
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!
Click to expand...
Click to expand...
 
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


Jeff said:
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.

Jacob Skaria said:
In report tab with cell a2 = department name

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

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


Jeff said:
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!
Click to expand...
Click to expand...
Click to expand...
 
I am getting reasonable results. I have to digest this sumproduct formula
(acts more like a sumifs to me). Thanks for the great help!!!!

Jacob Skaria said:
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


Jeff said:
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.

Jacob Skaria said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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!!!

Jacob Skaria said:
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


Jeff said:
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.

Jacob Skaria said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top