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

consjuly	marginjuly	Month
Peter	1000	July
Peter	500	July
John	1200	July
Sue	11	July
John	500	July
Peter	750	July
Peter	955	July
John	300	July
Sue	50	July

<tbody>
</tbody>

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.