Re: sumif for multiple criteria

Giganews Newsgroups
Subject: Re: sumif for multiple criteria
Posted by:  Dave Peterson (peters…@verizonXSPAM.net)
Date: Wed, 17 May 2006

=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

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

Replies

In response to

sumif for multiple criteria posted by Inter on Wed, 17 May 2006