Results 1 to 3 of 3

Thread: array formulas?

  1. #1
    Scottavs
    Guest

    array formulas?

    Office version: 9.0.3821SR1

    I am a Business Manager in the Retail Automotive Industry. I am not an Excel guru. I have been using Excel since 5.0 My problem is creating formulas with several arguments. I have never been successful. Is it even possible?

    I have a 12 month spreadsheet (13 pages in a workbook with a total year page) with 5 columns of "identifiers" for a vehicle transaction. "new & used, cash, lease, financed". I originally had two columns with "N/U" under one. "Cash, financed, lease" (c/f/l) used in another column. I could not get a sumif function to work asking it to identify transactions in "I" that were "N" and "J" that were "F". In other words I wanted to select only new and financed transactions. Then sum a column of figures of income under another column heading, example "reserve". This was being placed in a separate table on the same page of the workbook. I have tried IF, sumif, etc. but I can't get a formula to be accepted.

    My next thought was to create separate columns and use a value of "1" in each column as a marker, so I could use sumif function with true/false. It would then still sum ALL the figures in a column or return a value of zero.
    "If I=1 AND J=1, sum M" is my target, with only the numbers in M that have a value of 1 in I & J being summed.

    My main question is this even possible in Excel? Or do I have to go to Access and create a database? Would it be easier to create a separate table for each main catagory?Or, have I completely lost my self in the forest because I can't see the trees?

    Thanks in advance!
    bears@new.rr.com

  2. #2
    calacuccia
    Guest

    Re: array formulas?

    This is surely possible in Excel...

    I have attached a file showing you how to implement this.

    The data is simplified of course, but I have worked with 2 columns on sheet 'TwoColExample' and with 5 columns in 'FiveColExample'.

    In both cases I have used an array formula, which are very close to each other:

    In the case with 2 columns, I've used

    =SUM(IF(C2:C11="N",IF(D211="F",E2:E11)))

    In the case with 5 columns

    =SUM(IF(C2:C11=1,IF(F2:F11=1,H2:H11)))

    From your question, I've understood you don't know how to implement array functions although you've heared from it.

    Array formulas are typed as standard formulas in the formula bar, but are confirmed by hitting Ctrl+Shift+Enter together to enter them instead of just enter.

    Hope this helps
    Calacuccia

  3. #3
    Scottavs
    Guest

    Re: array formulas?

    Thanks,
    I can take it from there. The sample will save me a lot of grief!
    Scott

Posting Permissions

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