How to sum values based on Voucher no. when it's not repeating?

Bipin619

Member
Dear Experts,

PFA enclosed herewith. Is there any formula to get desired result? It's cumbersome process to sum each voucher manually.

Attachments

• 9.6 KB Views: 20

bosco_yip

Excel Ninja
Try,

In E2, copied down :

=IF((A3<>"")*(C2<>"")+(C3=""),IFERROR(1/(1/(SUM(D\$2:D2)-SUM(E\$1:E1))),""),"")

Regards
Bosco

Thanks Ninja

Royushken

New Member
Hi bosco_yip,

I'm curious about this formula, because I don't quite understand how it works. It appears the first segment to comprise the logical argument using the * are acting like an AND function, whereas I'm not sure what the + is doing in that segment. Also, would you mind explaining why you use the 1/ in the iferror fomula?

Thanks!

Try,

In E2, copied down :

=IF((A3<>"")*(C2<>"")+(C3=""),IFERROR(1/(1/(SUM(D\$2:D2)-SUM(E\$1:E1))),""),"")

Regards
Bosco

bosco_yip

Excel Ninja
To Royushken

1] In the IF logical testing argument, the * are acting like an AND function and the + are acting like an OR function

so,

=(A3<>"")*(C2<>"")+(C3="") is equal to =OR(AND(A3<>"",C2<>""),C3="")

and the purpose is to save 2 functions.

2] The IFERROR(1/(1/formula),"") purpose is to turn the formula resulted 0 to blank

Regards
Bosco