RECHERCHEV adds multiple rows with the same name

Solved
pfl.bourde Posted messages 13 Status Membre -  
pfl.bourde Posted messages 13 Status Membre -
Hello,

I have an Excel file with case numbers in column A, invoice numbers in column B, and invoice amounts in column C.
There can be several invoices for the same case number, so multiple rows for the same case. Example:

Case Invoice Amount
121212 011065 500.00
121212 021065 500.00
121212 031065 500.00

In another file where case numbers are listed, I would like to perform a VLOOKUP to retrieve the total amount of invoices by case.

PROBLEM: When I perform a VLOOKUP by case, the formula only takes into account the first line that contains this case number and does not sum all the lines.
VLOOKUP(121212;A2:C4,3,FALSE) = 500.00 and not 1500.00.

What formula could we use to sum the amount of all invoices corresponding to the case number 121212?

Thank you in advance for your answers!

Best regards.

1 réponse

Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453
 
Hello

=SUMIF(range of numbers; case number; range of amounts)

so according to your example:

=SUMIF(A2:A4;121212;C2:C4)

or if the reference is in Z1

=SUMIF(A2:A4;Z1;C2:C4)

best regards

--
The quality of the answer mainly depends on the clarity of the question, thank you!
0
pfl.bourde Posted messages 13 Status Membre
 
Thank you for your solution!

Simple, effective, I hadn't thought of that.

Have a good day!
0