sumif for multiple criteria
 

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

sumif for multiple criteria
 

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.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Inter" wrote:

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

sumif for multiple criteria
 

=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 he
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/assistan...lconPT101.aspx


Inter wrote:

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

--

Dave Peterson

sumif for multiple criteria
 

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.


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=543011