sumif for multiple criteria

[Guest]

I'm trying to sum the contents of multiple rows based on specifying 2
criteria, e.g.

downloads brand country
5 A US
10 A US
10 B UK
5 B UK
10 A UK
10 B US

And i want to add the downloads for brand B in the UK...

A bit of help would be much appreciated.
Thanks

 

[Guest]

Inter,

SUMPRODUCT may help.

Assuming you paste your data in top left corner of the sheet;

=SUMPRODUCT(A2:A7,IF(B2:B7="B",1,0),IF(C2:C7="UK",1,0))

But commit with CTRL-SHIFT-ENTER to make it an array formula.

Regards,

Chris.

 

[Dave Peterson]

=sumproduct(--(b1:b10="B"),--(c1:c10="UK"),(a1:a10))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

========
If you have lots of these to do, you may want to invest a little time in
learning about pivottables.

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[mr_teacher]

If you also wanted to be able to specify which brand and which country
you wanted to look at and avoid having to put a new formula in for each
one could you put the product code in cell e2 (e.g. B) and the country
code (e.g. UK)in cell f2 and then use the formula in g2

=SUMPRODUCT(--(B1:B10=E2),--(C1:C10=F2),(A1:A10))

This should allow you to specify any combination and see the total.