SUM LOOKUP Results

R

rustistic

New Member
Joined
Apr 20, 2011
Messages
2
Hi everyone,

I have searched all forums and I can't find the answer to this question, at least not one I understand. I will try to make this as clear as possible, so bare with me and please ask if I need to explain something further.

I have multiple columns, in the first column "Date" the second "Name" and in the third column "Value Aus$".

Example:
Date | Name | Value Aus$
01/01/2010 | Tom | $100
05/03/2010 | Mark | $70
06/03/2010 | Sam | $60
06/03/2010 | Tom | $50
08/03/2010 | Sam | $40
09/03/2010 | Tom | $90


** There are hundreds of rows of data and multiple entries for each name.

What I need to be able to do is look up a "Name" that is in a cell and return the SUM all the corresponding "Value Aus$".

Example:
Tom | =(SUM OF ALL "TOM" VALUES)
Sam | =(SUM OF ALL "SAM" VALUES)

In the example above, the sum for Tom would be =$240 and for Sam =$100.


Thank you for your help in advance :)
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Welcome to the board.

For example, if you have the 3 columns in A, B and C as described below, and in E1 you have the name Tom and E2 the name Sam, then try this formula in F1 (and drag down to F2):
Code: 
=SUMIF(B:B,E1,C:C)
e.g.
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 44px"><COL style="WIDTH: 71px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 35px"><COL style="WIDTH: 21px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt">Name</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt">Date</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt">Amount</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt">Tom</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">Tom</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">21/04/2011</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">1</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt">Sam</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">12</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">Sam</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">22/04/2011</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">Tom</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">23/04/2011</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">3</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">Sam</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">24/04/2011</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">4</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">Tom</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">25/04/2011</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">5</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">Sam</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">26/04/2011</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">6</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F1</TD><TD>=SUMIF($A$1:$A$7,E1,$C$1:$C$7)</TD></TR><TR><TD>F2</TD><TD>=SUMIF($A$1:$A$7,E2,$C$1:$C$7)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 
Upvote 0
Hello,

I have a very similar problem, using the same example above, if I wanted to find the sum of Tom and Sam but exclude rows 4 and 5, what changes would I make to the formula?
 
Upvote 0
JackDanIce said:
Welcome to the board.

For example, if you have the 3 columns in A, B and C as described below, and in E1 you have the name Tom and E2 the name Sam, then try this formula in F1 (and drag down to F2):
Code: 
=SUMIF(B:B,E1,C:C)
e.g.
Sheet1

ABCDEF
1NameDateAmount Tom9
2Tom21/04/20111 Sam12
3Sam22/04/20112
4Tom23/04/20113
5Sam24/04/20114
6Tom25/04/20115
7Sam26/04/20116

<colgroup></colgroup><colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 44px"><col style="WIDTH: 71px"><col style="WIDTH: 56px"><col style="WIDTH: 56px"><col style="WIDTH: 35px"><col style="WIDTH: 21px"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
F1=SUMIF($A$1:$A$7,E1,$C$1:$C$7)
F2=SUMIF($A$1:$A$7,E2,$C$1:$C$7)

<tbody>
</tbody>

<tbody>
</tbody>
Click to expand...


thx very much, small answer and solved my problem too :)
 
Upvote 0
Aladin Akyurek said:
What is the result which must obtain?
Click to expand...

Hi, sorry for the BUMP!

What's the easiest way to change this formula to only look for values within a specific date submission range?

For example, we have a similar set of data as in the OP however we also have a date that the record was submitted. How would we look for all the values submitted by Tom on the 1st April 2016?

Can you use something like the below:

'$A:$A,">="&$A$3,$A:$A,"<"&$A$3+1

To select the specific date range? If so how to I incorporate this into the original SUMIF formula?

Thanks for your time reading this and for any suggestions/solutions that you may have.

Thanks
 
Upvote 0
Sorry (couldn't find the Edit button), but just to clarify.

'$A:$A,">="&$A$3,$A:$A,"<"&$A$3+1

Where '$A:$A is the column where the date is located and $A$3 is the location of an arbitrary date in a separate tab that we are looking to run the whole query against.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Returning 1 or 0 based on multiple dates
Replies
2
Views
139
bebo021999
B
M
  • Question
Changing Month Only to Deadline Dates in Excel
Replies
3
Views
152
mflores775
M
A
  • Solved
Returning name on multiple date arrays
Replies
9
Views
443
archibell
A
S
  • Solved
Value of Most Recant Order
2
Replies
10
Views
415
FormR
FormR
A
  • Question
Need to convert value depending on currency and date
Replies
2
Views
226
deLockloire
D

Forum statistics

Threads
1,216,194
Messages
6,129,449
Members
449,509
Latest member
ajbooisen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top