Help needed with table formulas

[inddon]

Hello There,

I have 3 requirements with the table formulas. For ease, I have attached the sample workbook as well described the requirements.

Appreciate your help.

Look forward to hearing from you.

Regards,
Don

 

[bosco_yip]

Try..........

1] Count bill/invoice no J6, formula copy down :

=SUMPRODUCT((B$6:B$12=I6)*(LEFT(C$6:C$12,2)="10"))&"/"&SUMPRODUCT((B$6:B$12=I6)*(LEFT(C$6:C$12,2)="70"))

2] Bill no amount M6, formula copy down :

=SUMPRODUCT(($B$6:$B$12=$I6)*(LEFT($C$6:$C$12,2)=LEFT(L$6,2)),$E$6:$E$12)

3] Invoice amount O6, copy / paste formula from M6 and copy down.

Regards

 

[inddon]

Try..........

1] Count bill/invoice no J6, formula copy down :

=SUMPRODUCT((B$6:B$12=I6)*(LEFT(C$6:C$12,2)="10"))&"/"&SUMPRODUCT((B$6:B$12=I6)*(LEFT(C$6:C$12,2)="70"))

2] Bill no amount M6, formula copy down :

=SUMPRODUCT(($B$6:$B$12=$I6)*(LEFT($C$6:$C$12,2)=LEFT(L$6,2)),$E$6:$E$12)

3] Invoice amount O6, copy / paste formula from M6 and copy down.

Regards

Thanks for your help Bosco. The formula works good.

Regards,
Don