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

    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 {


  2. #2
    Join Date
    Sep 2008
    London, UK
    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:
    Even better, if you are using Excel 2007 or later, you can use COUNTIFS().
    If you're interested in reading more about array formulas, I wrote a detailed introduction tutorial here:

    If you're interested in reading more about SUMPRODUCT(), Bob Phillip's page is a very good resource:

    Hope that helps....
    Last edited by Colin Legg; 01-13-11 at 04: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