Relative Range Reference in a sumifs formula

cbotos

unread,

Mar 31, 2010, 1:50:01 PM3/31/10

to

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

Pete_UK

unread,

Mar 31, 2010, 1:59:23 PM3/31/10

to

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

cbotos

unread,

Mar 31, 2010, 3:11:02 PM3/31/10

to

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:

> .
>

T. Valko

unread,

Mar 31, 2010, 4:44:05 PM3/31/10

to

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" <cbo...@discussions.microsoft.com> wrote in message
news:B2B3A3E9-506E-4F31...@microsoft.com...

Ziggy

unread,

Mar 31, 2010, 5:16:26 PM3/31/10

to

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

Ziggy

unread,

Mar 31, 2010, 5:26:52 PM3/31/10

to

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.

T. Valko

unread,

Mar 31, 2010, 9:59:00 PM3/31/10

to

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" <zigg...@xmission.com> wrote in message
news:109fdc41-432a-46be...@10g2000yqq.googlegroups.com...