Selecting a names range based on variable in SUMIFS formula

[pete333]

Hi

I have the following formula:

=SUMIFS(marginjuly,conjuly,K$1)

where K$1 is the name of the consultant,


This currently looks at my data in "marginjuly" and adds up all the results of matching consultant

eg:

Consultant Name
[TABLE="width: 500"]
<tbody>[TR]
[TD]consjuly
[/TD]
[TD]marginjuly[/TD]
[TD]Month
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter
[/TD]
[TD]1000
[/TD]
[TD]July
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter
[/TD]
[TD]500
[/TD]
[TD]July
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]1200
[/TD]
[TD]July
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sue
[/TD]
[TD]11
[/TD]
[TD]July
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]500
[/TD]
[TD]July
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter
[/TD]
[TD]750
[/TD]
[TD]July
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter
[/TD]
[TD]955
[/TD]
[TD]July
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]300
[/TD]
[TD]July
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sue
[/TD]
[TD]50
[/TD]
[TD]July
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula nicely gets me the consultant margin for the month indicated, but i would like to be able to change the month and have the formula change with the month variable.

so august would need to look like this: =SUMIFS(marginaugust,conaugust,K$1)

How can I selected a range within the formula based on a date variable.

I know i could possibly change my raw data and simply use multiple criteria in the sumifs formula, however I already have this set up in names ranges throughout spreadsheet.

Thanks

Peter

 

[Marcelo Branco]

Hi Peter,

Welcome to Mr. Excel Forum.

I'm not sure if I understand what you need. See if this is Ok - assumes you have already set up named ranges for each month.

Since there is only one condition you can use SUMIF

=SUMIF(INDIRECT("con"&J1),K$1,INDIRECT("margin"&J1))

where J1 houses the month (July or August, for example) and K1 the name of consultant.

M.

 

[pete333]

Actually that is almost perfect for what I need....it works if J1 is a text field... only challenge now is J1 is a date field... I tried this:

=SUMIF(INDIRECT("con"&MONTH(J1)),K$1,INDIRECT("margin"&MONTH(J1)))

So I would need to convert the month value of J1 to Text string. eg 9=September. is there an easy way?

Thanks

Peter

Hi Peter,

Welcome to Mr. Excel Forum.

I'm not sure if I understand what you need. See if this is Ok - assumes you have already set up named ranges for each month.

Since there is only one condition you can use SUMIF

=SUMIF(INDIRECT("con"&J1),K$1,INDIRECT("margin"&J1))

where J1 houses the month (July or August, for example) and K1 the name of consultant.

M.

 

[Marcelo Branco]

Try this

=SUMIF(INDIRECT("con"&TEXT(J1,"mmmm")),K$1,INDIRECT("margin"&TEXT(J1,"mmmm")))

M.

 

[pete333]

Brilliant - that worked.

Have a super day!

Thank you

Try this

=SUMIF(INDIRECT("con"&TEXT(J1,"mmmm")),K$1,INDIRECT("margin"&TEXT(J1,"mmmm")))

M.

 

[Marcelo Branco]

Brilliant - that worked.

Have a super day!

Thank you

You are welcome and thanks for the feedback.

M.