We help IT Professionals succeed at work.

Simple Formula to add values when criteria met

gisvpn
gisvpn asked
on
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.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010
Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010
Commented:
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

Author

Commented:
Hello,

Could I ask how to use multiple conditions in the If statement?
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.