how to get the total from combination of index and match function

Hi there
i got 1 excel database.
i currently try to get the total but i dont have an idea how to solve it.
attach is my database.
i want total (master record Qty Out(example n7)). what i try to do is i want the total of n7 and the result will be display at I7. Meaning if item N7
is 3 times, i will get the total n7+n7+n7. I7 is the result of the total of N7 which i calculate from transaction out sheet.
transaction out is where i key in all the data and master record will calculate the total if it got the match result.
i hope someone can help me

note, if this thread already got the same post with others, can someone give me the link. thanks

thanks
abu

Hi Abu,

You can use below formula to get the sum for all matching records:-

=SUMPRODUCT(('Transaction out'!$Q$2:$Q$20='Master Record'!$N7)*('Transaction out'!$B$2:$B20))

see attached:- contoh inv movemnt.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

Thanks Abu,

For dynamic range, you can use defined names OR SumIF function as shared by Cutter... cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Abu,


See the formula part :-
'Transaction out'!$Q$2:$Q$20='Master Record'!$N7

Where two sheets are used which are "Transaction Out" and "Master Record" ... similarly you can add more references along with relevant criteria

Regards,
DILIPandey

<click on below 'star' if this helps>