Solved

Convert SUMIFS to work in Excel 2003

Posted on 2010-09-16
13
717 Views
Last Modified: 2012-05-10
=SUMIFS(H:H,A:A,J2,D:D,"Initiating User")

I am currently using excel 2007 but I am building some charts for other users, I used a LOT of SUMIFS in this workbook, but unfortunately this won't work on their computers due to having xl2003.  I wish everybody would just convert to 2007 but we have some stubborn folks here, any help converting this to work in 2003 would be appreciated

-Brandon
0
Comment
Question by:KnutsonBM
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 200 total points
ID: 33691730
Brandon,You can use SUMPRODUCT:=SUMPRODUCT((A1:A1000=J2)*(D1:D1000="Initiating User")*(H1:H1000))This use of SUMPRODUCT() depends on the function's ability to process arrays, andthe fact that Excel treats Boolean TRUE and FALSE as 1 and 0, respectively.  Thebest treatment I have ever seen for this usage of SUMPRODUCT() is at:http://xldynamic.com/source/xld.SUMPRODUCT.htmlIn 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 functionthen takes the sum of the individual products as its return value2) 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 (doubleminus sign) operator, Excel treats the Boolean value as 1 or 0Patrick
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 200 total points
ID: 33691741
Hello Brandon,
You can use SUMPRODUCT but you can't use the whole column with that function, so you need to restrict the range, something like this
=SUMPRODUCT(H2:H1000,(A2:A1000=J2)*(D2:D1000="Initiating User"))
adjust ranges as required
regards, barry
0
 
LVL 6

Author Comment

by:KnutsonBM
ID: 33691749
Do you have to specify the ROWs in the formula or can i use A:A, D:D, and H:H, these columns are in a linked query so the number of rows the table will cover will change over time
0
 
LVL 6

Author Comment

by:KnutsonBM
ID: 33691757
haha you guys must be psychic, answering my question before i ask it
0
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 100 total points
ID: 33691766
KnutsonBM,

Try

=SUMPRODUCT((A1:A65535=J2)*(D1:D65535="Initiating User")*H1:H65535)

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33691772
lots of xovers...
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33691782
In Excel 2003 you cannot use whole rows/columns for what are essentially array operations, which this is (even though it is not set up as an array formula).What you can do is set up dynamic Names.For example, create a Name, ColA, referring to (if the data are numeric or dates):='Sheet Name'!$A$1:INDEX('Sheet Name'!$A:$A,MATCH(10^300,'Sheet Name'!$A:$A))or if text:='Sheet Name'!$A$1:INDEX('Sheet Name'!$A:$A,MATCH("ZZZZZZZZZZZZZ",'Sheet Name'!$A:$A))Do the same for the other columns.  Now your fomula becomes:=SUMPRODUCT((ColA=J2)*(ColD="Initiating User")*(ColH))
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33691790
>>haha you guys must be psychic, answering my question before i ask itYes, well, when it's a formula question, we have to use black magic to overcome barry's supernatural powers :)
0
 
LVL 6

Author Comment

by:KnutsonBM
ID: 33691809
doh, i should have given credit to more than one
how do i reopen this..................sorry guys
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33691822
and even then we usually fail...
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33691826
KnutsonBM,No worries.  Just click 'request attention', and in the reason field ask the Mods to reopen the question :)Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33691841
Click the 'request attention' link in the original question box - explain what you want - that's all.

Patrick(ab)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Formula 6 47
Add a range in an Excel graph 5 36
Dynamic Filter ? 4 21
Help Updated Qtr 2 11
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now