Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

Convert SUMIFS to work in Excel 2003

=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
KnutsonBM
Asked:
KnutsonBM
  • 4
  • 4
  • 3
  • +1
3 Solutions
 
Patrick MatthewsCommented:
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
 
barry houdiniCommented:
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
 
KnutsonBMAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
KnutsonBMAuthor Commented:
haha you guys must be psychic, answering my question before i ask it
0
 
patrickabCommented:
KnutsonBM,

Try

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

Patrick
0
 
patrickabCommented:
lots of xovers...
0
 
Patrick MatthewsCommented:
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
 
Patrick MatthewsCommented:
>>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
 
KnutsonBMAuthor Commented:
doh, i should have given credit to more than one
how do i reopen this..................sorry guys
0
 
patrickabCommented:
and even then we usually fail...
0
 
Patrick MatthewsCommented:
KnutsonBM,No worries.  Just click 'request attention', and in the reason field ask the Mods to reopen the question :)Patrick
0
 
patrickabCommented:
Click the 'request attention' link in the original question box - explain what you want - that's all.

Patrick(ab)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now