need help to make a sumif formula working

The sumif had to meet two conditions as below.  I got an error for the following formula.  Please help.

=SUMIF(AND(Data!G:G = "SOUTHERN", Data!AD:AD ="FL"),Data!AI:AI)
jjxia2001Asked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
You can't do that.  Try:

=SUMPRODUCT((Data!G1:G1000="SOUTHERN")*(Data!AD1:AD1000="FL")*(Data!AI1:AI1000))

This use of SUMPRODUCT() depends on the function's ability to process arrays, and the fact that Excel treats Boolean TRUE and FALSE as 1 and 0, respectively.  The best treatment I have ever seen for this usage of SUMPRODUCT() is at:

http://xldynamic.com/source/xld.SUMPRODUCT.html

In summary:

1) SUMPRODUCT() will process each member of the designated arrays in turn, multiplying the corresponding values in each array.  As the name suggests, the function then takes the sum of the individual products as its return value

2) In this example, at least one of the arrays returns a TRUE or FALSE Boolean value. When this value is multiplied by a numeric value, or if you use the double unary (double minus sign) operator, Excel treats the Boolean value as 1 or 0

Note that in Excel 2007 the new SUMIFS function allows you to specify multiple criteria, and also to use whole column/row references for SUMPRODUCT.
0
 
jjxia2001Author Commented:
I forgot to mention that it is in Excel spreadsheet.
0
 
McOzCommented:
Try using SUMPRODUCT, like this:

=SUMPRODUCT((Data!G:G="Southern")*(Data!AD:AD="FL"),Data!AI:AI)

Good luck!
McOz
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Patrick MatthewsCommented:
McOz,

In Excel 2003, you cannot use whole column or whole row references in SUMPRODUCT expressions or in array formulae.  Those restrictions were lifted in Excel 2007.

Patrick
0
 
McOzCommented:
Thanks patrick, I didn't realize that restriction - I've been using 2007 and up for a little while now

McOz
0
 
gordon2011Commented:
Not sure if it's in Excel 2003 but I think what you're looking for is SUMIFS function

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,criteria2], …)

e.g.
=SUMIFS(Data!AI:AI, Data!G:G,"Southern", Data!AD:AD,"FL")
0
 
Patrick MatthewsCommented:
gordon2011,

SUMIFS debuted in Excel 2007.

Patrick
0
 
jjxia2001Author Commented:
It works! Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.