Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    35

    Unanswered: Excel formula query

    Some time ago I created a formula which works.

    {=SUM((RegularJobs!M2:RegularJobs!M222="A")*(Regul arJobs!J2:RegularJobs!J222="3.3"))}

    This provides a count of the number of rows where the M column has an 'A' in place and the J column has a '3.3' in place.

    Works a treat. However I've just attempted to produce the same formula in another cell and it doesn't work - why? Because the system somehow added the curly brackets { to the formula line last time and I can’t recall how I did this.

    Please for my sanity explain how to add the {

    Thanks

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    It's an array formula. To get the braces you have to complete the formula entry with CTRL+SHIFT+ENTER instead of just ENTER.

    This formula gives the same result, is a bit faster and does not have to be array entered:
    Code:
    =SUMPRODUCT(--(RegularJobs!M2:M222="A"),--(RegularJobs!J2:J222="3.3"))
    Even better, if you are using Excel 2007 or later, you can use COUNTIFS().
    Code:
    =COUNTIFS(RegularJobs!M2:M222,"A",RegularJobs!J2:J222,"3.3")
    If you're interested in reading more about array formulas, I wrote a detailed introduction tutorial here:
    http://www.xtremevbtalk.com/showthread.php?t=296012

    If you're interested in reading more about SUMPRODUCT(), Bob Phillip's page is a very good resource:
    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    Hope that helps....
    Last edited by Colin Legg; 01-13-11 at 05:34.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •