distribute the amount

[Geo Jul]

Dear All​

I need your help with a formula to distribute the amount in Columns C And D matching column B with Header F5 to L5​

Your prompt action is highly appreciated​

 

[Joe4]

Place this formula in cell E3 and copy all the way down to L10:

=IF($B3=E$2,SUM($C3:$D3),"")

 

[Geo Jul]

Place this formula in cell E3 and copy all the way down to L10:

=IF($B3=E$2,SUM($C3:$D3),"")

View attachment 104688

Many thanks for your time and great formula

 

[Joe4]

You are welcome.
Glad I was able to help!
The key is the mixed references in the ranges, and where the "$" are placed.

 

[Geo Jul]

You are welcome.
Glad I was able to help!
The key is the mixed references in the ranges, and where the "$" are placed.

many Thanks is being able to use INDEX and Match Function

 

[Joe4]

many Thanks is being able to use INDEX and Match Function

I am not sure what you are saying/asking.
Are you saying that you need/want to use an Index/Match formula?

 

[Geo Jul]

I am not sure what you are saying/asking.
Are you saying that you need/want to use an Index/Match formula?

yes if you please

 

[Joe4]

But why?
What is wrong with the first formula I gave you?
Does it not do something that you want?

To be honest, I do not use Index/Match that much, so I may not be the best person to answer that question.
But if you already have a solution that does exactly what you need, I am not sure why you need another one.

 

[Joe4]

And to follow-that up, I don't think it is going to be a shorter/simpler answer, as you may have to do two sets of formulas (one for column C and one for column D) and add them up, but then you will also need to account for the #N/A errors that INDEX/MATCH returns when it does not find a match.

So I would recommend sticking with the simpler solution.

 

[Geo Jul]

And to follow-that up, I don't think it is going to be a shorter/simpler answer, as you may have to do two sets of formulas (one for column C and one for column D) and add them up, but then you will also need to account for the #N/A errors that INDEX/MATCH returns when it does not find a match.

So I would recommend sticking with the simpler solution.

thank you for your time and help Regards