Relative Range Reference in a sumifs formula

  • Thread starter Thread starter cbotos
  • Start date Start date
C

cbotos

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
 
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!
 
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)
 
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)))
 
Back
Top