Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72

    Unanswered: =SUMPRODUCT((Data1!$B$1:$B$500="March")*(Data1!$C$ 1:$C$500="New York"),(Data1!$E$1:$E

    Hi

    =SUMPRODUCT((Data1!$B$1:$B$500="March")*(Data1!$C$ 1:$C$500="New York"),(Data1!$E$1:$E$500))

    In plain english the formula above means: Sum cells E1 to E500 (last argument) if in B1 to B500 the value is "March" and if in C1 to C500 the value is "New York".

    SUMPRODUCT is like SUMIF on steroids and it allows you to automate all these reports that you develop with data that you import from a large centralized database, centralized accounting, manufacturing and sales programs or the Internet.

    The last argument (preceded by a comma) tells Excel what to sum and the two previous arguments (separated by an asterisk) tell Excel about the conditions for summing.

    You can do this with SUMPRODUCT and much more.

    Within a table where months are in column "A" and cities in row "11" the formula would read like this

    =SUMPRODUCT((Data1!$B$1:$B$500=$A13)*(Data1!$C$1:$ C$500=B$11),(Data1!$E$1:$E$500))

    Visit: SUMPRODUCT in Excel spreadsheets

    Rediscover Excel and enjoy
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Yes, integer multiplication is equivalent to boolean conjunction. But SUMPRODUCT only allows multiplication, so your kludge fails if you want an expression more complex than "this and this and that."

    Your site doesn't even explain why your technique works the way it does, or that a VB function could work just as well. And why are you advertising a kludge like SUMPRODUCT when there are better ways to achieve the same results?

    I can't imagine your book is worth $5, let alone $50. What a load of garbage.

Posting Permissions

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