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="AD"),--(B2:B20="Jan"),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)

"JP" wrote in message
...
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?

use
=sumproduct((a2:a22="ad")*(b2:b22="Jan")*c2:c22)

--
Don Guillett
SalesAid Software

"JP" wrote in message
...
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 is probably best but if you want to use SUMIF you could use
this array formula.

{=SUM(IF($A$1:$A$6="AD",IF($B$1:$B$6="Jan",$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: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=511114

Need SUMPRODUCT
=SUMPRODUCT(--(A1:A100="AD"),--(B1:B100="Jan"),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
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

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

"JP" wrote in message
...
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?