CODEWITHSUNDEEP
excelvlookup
Umar.H
Umar.H

Reputation: 23099

Vlookup and sum all instances of the matching lookup

this is probably an extremely basic problem but I can't figure a way to do this within Excel.

I have a list of values IDs from a regional sales manager,

he wants to see if his figures have been inputted into the monthly report and are reporting correctly.

However, as he sells by region and we report by product we have multiple instances of his ID by Product

so our monthly report.. for example 

ID  Value
1   16,999
1   22,521
3   400
3   221
5   71
6   22,000

So he has provided me with a list of IDs 

1
2
3
4
5
6

and wants a list with its total revenue not just the first match up.

Is an excel limitation or is there a way to do this?

Upvotes: 1

Views: 6100

Answers (2)

Monofuse
Monofuse

Reputation: 827

You can use a SUMIF formula. Basically you give it the column to check the value of, then you give it the expected value and finally the colum to sum.

Option 1 (whole range)

=SUMIF(A:A, 1, B:B)

Option 2 (defined range)

=SUMIF(A1:A7, 1, B1:B7)

Option 3 (Using excel table)

=SUMIF([Id], 1, [Value])

For more details please refer to: https://support.office.com/en-ie/article/sumif-function-169b8c99-c05c-4483-a712-1697a653039b

Upvotes: 1

Kate Hanahoe
Kate Hanahoe

Reputation: 156

Is this what you're looking for?

=SUMPRODUCT((A1:A5=1)*B1:B5)

Taken from here: Sum values from multiple rows using vlookup or index/match functions

How it works:

An array formula is used to create an array of numbers that gets passed to the SUMPRODUCT formula.

The array formula processes A1 and B1 together, then A2 and B2 and so on... if A1 is equal to 1, B1 is added to the array.

Upvotes: 1

Related Questions