software

I have several columns of data. One column is a persons initials, the second
column is months (Jan, Feb, etc) and the third column is revenue. I am
trying to do a SUMIF (or SUMIF(AND) formula that will look at columns 1 and 2
and then sum from column 3.

Example,

A B C
AD Jan 10
AD Jan 10
AD Feb 25
DB Mar 50
KV Apr 75

I need the formula to add up all the revenue in January associated with AD.

Can this be done?

=SUMPRODUCT(--(A2:A20=quot;ADquot;),--(B2:B20=quot;Janquot;),C2:C20)

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;JPquot; gt; wrote in message
...
gt; I have several columns of data. One column is a persons initials, the
second
gt; column is months (Jan, Feb, etc) and the third column is revenue. I am
gt; trying to do a SUMIF (or SUMIF(AND) formula that will look at columns 1
and 2
gt; and then sum from column 3.
gt;
gt; Example,
gt;
gt; A B C
gt; AD Jan 10
gt; AD Jan 10
gt; AD Feb 25
gt; DB Mar 50
gt; KV Apr 75
gt;
gt; I need the formula to add up all the revenue in January associated with
AD.
gt;
gt; Can this be done?
use
=sumproduct((a2:a22=quot;adquot;)*(b2:b22=quot;Janquot;)*c2:c22)

--
Don Guillett
SalesAid Software

quot;JPquot; gt; wrote in message
...
gt;I have several columns of data. One column is a persons initials, the
gt;second
gt; column is months (Jan, Feb, etc) and the third column is revenue. I am
gt; trying to do a SUMIF (or SUMIF(AND) formula that will look at columns 1
gt; and 2
gt; and then sum from column 3.
gt;
gt; Example,
gt;
gt; A B C
gt; AD Jan 10
gt; AD Jan 10
gt; AD Feb 25
gt; DB Mar 50
gt; KV Apr 75
gt;
gt; I need the formula to add up all the revenue in January associated with
gt; AD.
gt;
gt; Can this be done?

SUMPRODUCT is probably best but if you want to use SUMIF you could use
this array formula.

{=SUM(IF($A$1:$A$6=quot;ADquot;,IF($B$1:$B$6=quot;Janquot;,$C$1:$C $6,0),0))}

Commit with Ctrl-Shift-Enter which automatically puts the curly
brackets around the formula, do not type them yourself.

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=511114Need SUMPRODUCT
=SUMPRODUCT(--(A1:A100=quot;ADquot;),--(B1:B100=quot;Janquot;),C1:C100)
You could have initial in K2:K10 and months in L1:W1
in L2 use =SUMPRODUCT(--(A1:A100=$K2),--(B1:B100=L$1),C1:C100)
and copy this down and across to get all values
OR read about Pivot Tables in Help

SUMPRODUCT explained:
SUMPRODUCT
www.xldynamic.com/source/xld.SUMPRODUCT.html

mcgimpsey.com/excel/formulae/doubleneg.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;JPquot; gt; wrote in message
...
gt;I have several columns of data. One column is a persons initials, the
gt;second
gt; column is months (Jan, Feb, etc) and the third column is revenue. I am
gt; trying to do a SUMIF (or SUMIF(AND) formula that will look at columns 1
gt; and 2
gt; and then sum from column 3.
gt;
gt; Example,
gt;
gt; A B C
gt; AD Jan 10
gt; AD Jan 10
gt; AD Feb 25
gt; DB Mar 50
gt; KV Apr 75
gt;
gt; I need the formula to add up all the revenue in January associated with
gt; AD.
gt;
gt; Can this be done?