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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
NameDateAmount Tom
Sam

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]Tom[/TD]
[TD="align: right"]21/04/2011[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]Sam[/TD]
[TD="align: right"]22/04/2011[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]Tom[/TD]
[TD="align: right"]23/04/2011[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]Sam[/TD]
[TD="align: right"]24/04/2011[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]Tom[/TD]
[TD="align: right"]25/04/2011[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]Sam[/TD]
[TD="align: right"]26/04/2011[/TD]
[TD="align: right"]6[/TD]

</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
  • Solved
vlookup true range lookup
Replies
5
Views
277
etaf
etaf
I
  • Solved
Hyperlink file in pdf format
Replies
6
Views
482
John_w
J
A
  • Question Question
Multiply an array based on date value entered.
Replies
8
Views
160
Fluff
Fluff
A
  • Locked
  • Question Question
Multiply an array based on date value entered.
Replies
1
Views
56
Fluff
Fluff
J
  • Solved
Return last date checked
Replies
3
Views
73
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,602
Messages
6,179,845
Members
452,948
Latest member
UsmanAli786

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