Solved

need help to make a sumif formula working

Posted on 2011-09-28
8
273 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

707 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