dynamic sumif function

[loh_patrick]

Hi

I'm trying to create a sumif formula that finds the column the information is
in that it needs to sum. I have attached a copy of the file I'm trying to create a formula to get results that I want.

Generally you would normally use the "=SUMIF(range,criteria,sumrange)". I was wondering if there was a formula I can substitute "sumrange" to also search a range of data (Eg: "G16:DA28"). I need the range to be dynamic as the column to be sum can change based on the criteria/option button being selected from the product category. I assume there is an indirect function involved, but I am not familiar with it. If you have a better and less complicated formula please suggest.

This formula have been driving me up the wall and I appreciate any help I can get on this.

Also, I appreciate if you can give me the formula string for ease of reference and testing as I'm not an expert in excel. So try to be as detail as you can.

Thank you in advance!!!

Cheers
Patrick

[JBeaucaire]

Here's one of many ways, this way uses your INDIRECT() approach.

[loh_patrick]

Hi JBeaucaire

I don't think that the formula sheet you sent me is workable for me. My problem is more complicated than that.

I have attached a mock copy of my file to illustrate the complexity of the file and what I'm trying to do.

Appreciate your effort trying to solve this.

Cheers
Patrick

[JBeaucaire]

What's the answer supposed to be and where did you collate your answer from?

[JBeaucaire]

NVM, I see what you're doing.

Name range - G28:DA28 = CostOfSales
Name range - G4:DA4 = Names

Summary report - H30: =INDEX(CostOfSales,MATCH(H21,Names,0))-SUM(H27:H29)

[loh_patrick]

OMG!!! YOU'RE A GENIUS!!!

Thank you so much for the formula. It was so simple.

But for future reference, is there a way I can use a formula to return the same result?? Say if I have more than 1 P&L item which has the same name other than "SC/Others" (E.g. SC/Others and Other COS). If I replace "Other COS" for items in cell C24:C27 in the "Curr.Mth.Actual" worksheet, I wouldn't be able to use your formula for this. It would be great if you can let me know if you know of a formula that I could use for that.

Thanks a million again JBeaucaire!!!!!!

Cheers
Patrick

[JBeaucaire]

You'll have to mockup your actual need when it occurs and post it up as a new question.