Hi,

I have a worksheet full of data (Actual DT)with cost centers in column A,
subaccounts in column B, and data in columns D through O (each column is a
different month). In the main tab of my report, I have a dropdown box for
users to select the month they would like to see data for. In yet another
tab, I am trying to create a formula that is a 'sumifs' into the data sheet
that will sum the given month's column if both the cost center and subaccount
match those given in cells on that sheet.

So far, I have gotten to the following: =SUMIFS('Actual DT'!K:K,'Actual
DT'!A:A,A10,'Actual DT'!B:B,$A$9) (I have the CC i would like to match to in
A10 and the Subaccount in A9)

This formula works fine but what I would like to do is find some way to
remove the 'Actual DT'!K:K reference and have that be either a vlookup or
something into another sheet where I can lookup the month currently selected
in the main dropdown window and then have the corresponding sum range I want
to put into the sumifs statement in the next column over. I would even settle
for being able to reference one cell that I could format to hold the data
range for the given month. I just can't figure it out! I have had sucess
referencing the column number in a vlookup as a reference to another cell,
but I haven't figure out how to do this for a range of cells.

Is this possible? Please let me know if I can clarify anything.

Thank you in advance!!!

What cell in the drop-down on your main sheet is used to select the
month? Is this the name of the month (Jan or January), or is it a
number to represent the month?

Pete

On Mar 31, 6:50*pm, cbotos wrote:
Hi,

I have a worksheet full of data (Actual DT)with cost centers in column A,
subaccounts in column B, and data in columns D through O (each column is a
different month). In the main tab of my report, I have a dropdown box for
users to select the month they would like to see data for. In yet another
tab, I am trying to create a formula that is a 'sumifs' into the data sheet
that will sum the given month's column if both the cost center and subaccount
match those given in cells on that sheet.

So far, I have gotten to the following: =SUMIFS('Actual DT'!K:K,'Actual
DT'!A:A,A10,'Actual DT'!B:B,$A$9) (I have the CC i would like to match to in
A10 and the Subaccount in A9)

This formula works fine but what I would like to do is find some way to
remove the 'Actual DT'!K:K reference and have that be either a vlookup or
something into another sheet where I can lookup the month currently selected
in the main dropdown window and then have the corresponding sum range I want
to put into the sumifs statement in the next column over. I would even settle
for being able to reference one cell that I could format to hold the data
range for the given month. I just can't figure it out! I have had sucess
referencing the column number in a vlookup as a reference to another cell,
but I haven't figure out how to do this for a range of cells.

Is this possible? Please let me know if I can clarify anything.

Thank you in advance!!!

The main sheet is a tab called "SCORECARD" and the cell with the dropdown is
B7. The dropdown has users choose a month by full name (ex. January,
February, March, April, etc.)

i experimented with using a working sheet to pull the month selected from
Scorecard B7 and have the sumifs formula use an indirect into there but I
didn't have any luck.

Any help would be appreciated!

"Pete_UK" wrote:

What cell in the drop-down on your main sheet is used to select the
month? Is this the name of the month (Jan or January), or is it a
number to represent the month?

Pete

On Mar 31, 6:50 pm, cbotos wrote:
Hi,

I have a worksheet full of data (Actual DT)with cost centers in column A,
subaccounts in column B, and data in columns D through O (each column is a
different month). In the main tab of my report, I have a dropdown box for
users to select the month they would like to see data for. In yet another
tab, I am trying to create a formula that is a 'sumifs' into the data sheet
that will sum the given month's column if both the cost center and subaccount
match those given in cells on that sheet.

So far, I have gotten to the following: =SUMIFS('Actual DT'!K:K,'Actual
DT'!A:A,A10,'Actual DT'!B:B,$A$9) (I have the CC i would like to match to in
A10 and the Subaccount in A9)

This formula works fine but what I would like to do is find some way to
remove the 'Actual DT'!K:K reference and have that be either a vlookup or
something into another sheet where I can lookup the month currently selected
in the main dropdown window and then have the corresponding sum range I want
to put into the sumifs statement in the next column over. I would even settle
for being able to reference one cell that I could format to hold the data
range for the given month. I just can't figure it out! I have had sucess
referencing the column number in a vlookup as a reference to another cell,
but I haven't figure out how to do this for a range of cells.

Is this possible? Please let me know if I can clarify anything.

Thank you in advance!!!

.

Assuming in sheet Actual DT D1:O1 are the monthly column headers in the form
January, February, March, etc.

=SUMIFS(INDEX('Actual DT'!D:O,,MATCH(B7,'Actual DT'!D1:O1,0)),'Actual
DT'!A:A,A10,'Actual DT'!B:B,A9)

--
Biff
Microsoft Excel MVP


"cbotos" wrote in message
...
The main sheet is a tab called "SCORECARD" and the cell with the dropdown
is
B7. The dropdown has users choose a month by full name (ex. January,
February, March, April, etc.)

i experimented with using a working sheet to pull the month selected from
Scorecard B7 and have the sumifs formula use an indirect into there but I
didn't have any luck.

Any help would be appreciated!

"Pete_UK" wrote:

What cell in the drop-down on your main sheet is used to select the
month? Is this the name of the month (Jan or January), or is it a
number to represent the month?

Pete

On Mar 31, 6:50 pm, cbotos wrote:
Hi,

I have a worksheet full of data (Actual DT)with cost centers in column
A,
subaccounts in column B, and data in columns D through O (each column
is a
different month). In the main tab of my report, I have a dropdown box
for
users to select the month they would like to see data for. In yet
another
tab, I am trying to create a formula that is a 'sumifs' into the data
sheet
that will sum the given month's column if both the cost center and
subaccount
match those given in cells on that sheet.

So far, I have gotten to the following: =SUMIFS('Actual DT'!K:K,'Actual
DT'!A:A,A10,'Actual DT'!B:B,$A$9) (I have the CC i would like to match
to in
A10 and the Subaccount in A9)

This formula works fine but what I would like to do is find some way to
remove the 'Actual DT'!K:K reference and have that be either a vlookup
or
something into another sheet where I can lookup the month currently
selected
in the main dropdown window and then have the corresponding sum range I
want
to put into the sumifs statement in the next column over. I would even
settle
for being able to reference one cell that I could format to hold the
data
range for the given month. I just can't figure it out! I have had
sucess
referencing the column number in a vlookup as a reference to another
cell,
but I haven't figure out how to do this for a range of cells.

Is this possible? Please let me know if I can clarify anything.

Thank you in advance!!!

.

I like to create two colums off to the right of the financial data
with a 'CHOOSE' formula, say columns Q & R

One, with a simple 'CHOOSE' formula to select the current month or the
month you want to select, and
Two, also with a cummulative CHOOSE formula a year-to-date column
based on the selected month.

=CHOOSE(ref_cell,D5,sum($D5:E5), sum($D5:F5)... Sum($D5:O5))

It's then simple to reference only those two columns for your data.

Your SUMIFS formula is tied only to these columns

Bill, your formula works great.

How would you modify that to also yield a YTD result, i.e., Aug is
selected it sums Jan through Augus from the same dropdown.

You'd have to use a different function.

A2:A15 = cost center
B2:B15 = account codes
D1:O1 = column headers as month names (January, February, March, etc.)

Lookup values:

A17 = some cost center
B17 = some account code

A18 = drop down list with the month names (January, February, March, etc.)

=SUMPRODUCT((A2:A15=A17)*(B2:B15=B17)*D2:D15:INDEX (D2:O15,,MATCH(A18,D1:O1,0)))

--
Biff
Microsoft Excel MVP


"Ziggy" wrote in message
...
Bill, your formula works great.

How would you modify that to also yield a YTD result, i.e., Aug is
selected it sums Jan through Augus from the same dropdown.