?
Solved

Simple Formula to add values when criteria met

Posted on 2012-08-12
4
Medium Priority
?
367 Views
Last Modified: 2012-08-12
Hello,

I have data in the table as below :

London                    4
New York                 5
London                    9
London                    4
London                    4
New York                 5
New York                 5

I would like a formula (which will be on another sheet) to add up the total for London and New York :

Therefore London's formula would total 21 and New York would be 15 when using the formula.

I believe SUMPRODUCT maybe the one to use here but I am not sure how? Any help would be appreciated.
0
Comment
Question by:gisvpn
  • 3
4 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 38285123
Hello,

no need for Sumproduct. With just one criterion, Sumif() will do nicely

=SUMIF(Sheet1!A1:A7,"London",Sheet1!B1:B7)

For more than one criterion, you can use Sumifs() in Excel 2007 and later.

cheers, teylyn
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 38285125
And, of course, a Pivot table can do the whole thing without a single formula. Make sure your data table has headers, then click a cell in the table and use Insert > Pivot Table. Drag the city  column name into the row are and the value column name into the Values area. See attached.

cheers, teylyn
Book2.xlsx
0
 

Author Comment

by:gisvpn
ID: 38285132
Hello,

Could I ask how to use multiple conditions in the If statement?
0
 
LVL 50
ID: 38285149
There is no IF statement. The function is called SUMIF() an it takes only one condition.

For more than one condition you can use Sumifs in Excel 2007 and later

=SUMIFS(Sheet1!$C$2:$C$8,Sheet1!$A$2:$A$8,A2,Sheet1!$B$2:$B$8,B2)

In Excel 2003 and earlier, you will need Sumproduct

=SUMPRODUCT(--(Sheet1!$A$2:$A$8=A2),--(Sheet1!$B$2:$B$8=B2),Sheet1!$C$2:$C$8

See attached.

cheers, teylyn
Book2.xlsx
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question