Sum with two criteria

[Thomas Kuriakose]

Respected Sir,

Kindly find attached a sheet with the requirement of sum and pro-rata for values with criteria 10 and 20.

Cells C2: C15 should apportion values based on criteria 10 and value in H4

The sequence is the same for other criterial

Kindly let me know is there a better formula to get the result. I have done this manually by sum of checking how many values are in criteria 10 and then each value is divided by the total to get the apportion value.

Kindly check.

Thanks,

with regards,
thomas

 

[Chihiro]

Are you sure you are using right value/calculation for C41? It's definitely not =B41/SUM($B$41:$B$67)*$H$4= 17,667.31 nor is it =B41/SUM($B$41:$B$67)*$H$44 = 55,545.88

Anyways, formula in C2 can be something like...
=B2/SUMIF($A$2:$A$39,A2,$B$2:$B$39)*(CHOOSE(MATCH(A2,{10,20},0),$H$4,$F$4))

 

[Thomas Kuriakose]

Respected Sir,

Yes Sir, you are correct, apologies for this error, the calculation for C41 is based on value in F44 & H44 and not F4 & H4.

This is simply amazing. Thank you very much for the neat formula provided.

Sir, can we have combined formula to take the values for F44 & H44 with F4 & H4.

Thank you so much,

with regards,
thomas

 

[Chihiro]

In current format, there's really nothing that separates Row 2 to 39 from 41 to 95, other than break at row 40.

I'm not sure what separates the two. I'd recommend setting up helper column to identify and use that to control your calculation/referenced cell.

 

[Thomas Kuriakose]

Respected Sir,

The total values for pro-rata are different in both the ranges.

F4 = 428039
H4 = 533324

F44 = 2042588
H44 = 1676765

Thanks you once again,

with regards,
thomas

 

[Chihiro]

I don't see immediate logic operation that separates Type 10 in Row 2 to 39 rang from that of 41 to 95 range (or for Type 20).

You can just enter following in C41 and copy down.
=B41/SUMIF($A$41:$A$95,A41,$B$41:$B$95)*(CHOOSE(MATCH(A41,{10,20},0),$H$44,$F$44))

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this solution provided.

Thanks once again,

with regards,
thomas