Solved

need help to make a sumif formula working

Posted on 2011-09-28
8
267 Views
Last Modified: 2012-08-14
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)
0
Comment
Question by:jjxia2001
  • 3
  • 2
  • 2
  • +1
8 Comments
 

Author Comment

by:jjxia2001
ID: 36717472
I forgot to mention that it is in Excel spreadsheet.
0
 
LVL 9

Expert Comment

by:McOz
ID: 36717570
Try using SUMPRODUCT, like this:

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

Good luck!
McOz
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36717585
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36717593
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
 
LVL 9

Expert Comment

by:McOz
ID: 36717619
Thanks patrick, I didn't realize that restriction - I've been using 2007 and up for a little while now

McOz
0
 
LVL 1

Expert Comment

by:gordon2011
ID: 36717707
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36717744
gordon2011,

SUMIFS debuted in Excel 2007.

Patrick
0
 

Author Closing Comment

by:jjxia2001
ID: 36718239
It works! Thanks!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My experience with Windows 10 over a one year period and suggestions for smooth operation
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question