Calculate the Range based on certain values

[acsishere]

Dear Friends,

I would like to sum / calculate the range based on certain values of each cell.

I attached a workbook for your kind ref.

Please help me to solve.

[AZ-XL]

Ok
I made a little change on table. Replaced Category "A" header with letter "A" and etc.

r4 cell =SUM(IF(LEFT($C4:$Q4,1)=R$3,MID($C4:$Q4,2,1),-1)+1) Ctrl Shift enter
then drag it to right and down.

If its correct then Click to star

[acsishere]

Dear Sir,

Thanks for the solution.

However, I want to change the values in K11:K13, so that the value of the SUM may be changed.

For example, if I change the value of A1A to "5.25" then the SUM of the related rows also must be changed.

Request your help.

[cheal2]

Here this I think will get the desired results.

Changing the values of the numbers require more if statements

Hope this helps.

[AZ-XL]

Hi, Withe the changing values I got no result. I used there helper rows and lookup table.
Uploading file here for you to examine

[acsishere]

Dear Sir,

Thanks and it works.

Yet, is there any possibility to get the desired result without creating "helper rows" for each and every row?

Any solution will be there?

Please...

[acsishere]

Dear Sir,

Thanks and it works.

Yet, is there any possibility to get the desired result without creating "helper rows" for each and every row?

Any solution will be there?

Please...

[acsishere]

Dear Sir,

Any solution - without application of header row for each line item?

[acsishere]

Dear Sir,

Any solution - without applying the header row for each line item?

[cheal2]

Pretty much no way around for getting around the helper rows for the letter and number. It's really not a problem because you can have a data/calc sheet and then have a separate sheet for your summary if you're worried about presentation.

[AZ-XL]

ok acsishere
I think I solved your problem without header rows. Here is the array formula
=IFERROR(SUM(LARGE($X$3:$X$5,RANK(LARGE(IFERROR(--(IF(LEFT($C4:$Q4,1)=R$3,MID($C4:$Q4,2,1),"")),""),ROW(INDIRECT("1:"&SUM(--ISNUMBER(IFERROR(--(IF(LEFT($C4:$Q4,1)=R$3,MID($C4:$Q4,2,1),"")),"")))))),$W$3:$W$5))),0)

I think this time you should click Star.

[acsishere]

Dear Sir,

Thanks for your extraordinary efforts.

Still, when tried to apply the formula with changes in the value, the formula is not picking up.

If I put the fractions in the Y column, then no changes are done in the result.

I attached the sample for your kind reference.

[acsishere]

Dear Sir,

Please help......

[AZ-XL]

Dear Sir,

Thanks for your extraordinary efforts.

Still, when tried to apply the formula with changes in the value, the formula is not picking up.

If I put the fractions in the Y column, then no changes are done in the result.

I attached the sample for your kind reference.

Numbers there should be ranked. Number 2 must have max valu, 1 middle value and 0 min value. Otherwise it will not work.

Any other solution I cannot give you, you may ask others. Surely Vba experts here can help you

[acsishere]

Dear Friends,

Any VBA solution? Though the formula serves the purpose, due to its lengthiness, it takes more time to do calculation. I fear If the size of the file increased, then it will be difficult.

Kindly help to find a VBA solution.

[acsishere]

Dear Friends,

Please help me.

[AZ-XL]

I suggest you to mark this thread as solved and create new post. If you want the VBA solution then create it in VBA section.

Good luck

[arlu1201]

Please do not create another thread in the programming / vba section. I have moved this thread to that section for you.

[acsishere]

Thanks for your moving my thread to VBA section.

Kindly help me to find a solution.

[acsishere]

Dear Friends,

Hope I may get a solution for this.

[acsishere]

Dear Friends,

Please....

[acsishere]

Please.... Please.... Please....

[AZ-XL]

My friend, I wish I knew the VBA But I do not.
I did my best in fnding formula solution for your problem. Hope others will help you.

Good luck

[acsishere]

Dear Sir,

Thanks for your reply. As said, I hope someone will help me to find the solution.

[acsishere]

Request kind help.

[acsishere]

Please.....

[AZ-XL]

I think you should close this thread and open new one. Just try

[acsishere]

Dear Sir,

You are right. But moderators may not allow this.

Alternatively, I give the link here, in which I requested the VBA modifications in an attached file.

The link is:

http://www.excelforum.com/showthread...=1#post3208947

Hope to get the desired result.

[acsishere]

Kindly help me to find the solution.

[acsishere]

Dear Friends,

I hope that someone will lend helping hand.

http://www.excelforum.com/showthread...=1#post3234381

Please....

[acsishere]

Dear Friends,

I don't know how to solve the required query. I tried all probabilities, but in vain.

It seems that I must close this thread, if there is no objection from the moderators, and to start a new one, either in this forum or in some other forum.

As I don't know what to do, I request the moderators to guide me.

[acsishere]

Dear Sir,

As I have not got any response, can I close this thread & open in a new forum? Request moderator's consent.

[acsishere]

Dear Moderator,

I request your kind consent to post this thread in some other forum to get the solution. Because, I feel that I don't want to be penalized for any reason from this EXCELlent forum.