1

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?

Umar.H
  • 22,559
  • 7
  • 39
  • 74
  • 2
    You may use a pivot table on the first table to aggregate by `ID`. Then just do a normal `VLOOKUP` of the second table on the pivot table aggregate. – Tim Biegeleisen Apr 11 '18 at 14:56
  • Use `SUMIF` https://support.office.com/en-us/article/sumif-function-169b8c99-c05c-4483-a712-1697a653039b – TomJohn Apr 11 '18 at 14:57

2 Answers2

1

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

TomJohn
  • 747
  • 6
  • 19
Monofuse
  • 735
  • 6
  • 14
1

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.

Kate Hanahoe
  • 166
  • 8
  • 1. But why overcomplicate this... 2. Please format your code. – TomJohn Apr 11 '18 at 15:12
  • @TomJohnRiddle don't think it's too complicated – Kate Hanahoe Apr 11 '18 at 15:26
  • If I understand the logic here, I'm returning the value of the ID but multiplying each instance of it? – Umar.H Apr 11 '18 at 15:33
  • 1
    @KateHanahoe after second though I think that this answer has some important value in it. You cannot use SUMIF'S in Excel 2003 while you can use SUMPRODUCT (SUMIF which is enough for this case is available in Excel 2003). In general some sources (http://www.exceluser.com/blog/483/excels-sumifs-or-sumproduct-which-is-faster.html) state that SUMPRODUCT is much less efficient than SUMIFS – TomJohn Apr 11 '18 at 15:59