So, who knows about Array formulas in Excel?

Discussion in 'Mac Apps and Mac App Store' started by oli2140, Sep 2, 2010.

  1. oli2140 macrumors 6502

    Joined:
    Jan 13, 2008
    #1
    Hi everyone,

    I'm lucky enough to be in Excel 2007 and have infront of me 3 columns of data.

    As an example, in the first column is the colour of a product, 2nd = the price, 3rd column = quantity in stock.

    Now I know that I can use an array formula to come up with a way of finding out the number of red products that are £2 and have 2 items in stock - unfortunately I am not sure how to do this!

    Anybody have any tips/ways of doing this?

    Thanks so much if you're brave enough to answer!
     
  2. oli2140 thread starter macrumors 6502

    Joined:
    Jan 13, 2008
    #2
    So far I've got:

    {=SUM(IF(AND(A2:A100="Red",B2:B100=2),1,0))}

    Trying to go for just the colour red and £2 but so far no luck :rolleyes:

    Ok I've just worked it out, for those that have a massive interest in this kind of thing, the way to do it is:

    =Sum(IF((A3:100="red")*(B3:B100=2),1,0))
     
  3. WesCole macrumors 6502a

    Joined:
    Jul 1, 2010
    Location:
    Texas
    #3
    This may not be what you want to hear, but have you tried creating a list?

    Just right-click in the data and select "Create List" and then you will be able to filter by your headers by just clicking on them and selecting the criteria you want.

    On another note, make yourself a database with Access and save a lot of time. :)

    Trust me, I used to work exclusively in spreadsheet for years until I realized how much more efficient databases can be with their queries and macros.

    Hope this helps a little.

    Wes
     
  4. WesCole macrumors 6502a

    Joined:
    Jul 1, 2010
    Location:
    Texas
    #4
    Do you have a screenshot of the file? Or, better yet, a copy of the file? I wouldn't mind looking at it and letting you know what I figure out.

    Wes
     
  5. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #5
    See the end of his second post. He figured this out hours ago.
     
  6. WesCole macrumors 6502a

    Joined:
    Jul 1, 2010
    Location:
    Texas
    #6
    Guess I got excited that I actually found a topic I could help with and didn't read carefully enough. Haha.
     

Share This Page