Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUMIF or array on two criterion (XP)

    (Edited by HansV to format tables)

    I have a table with four columns: date, payee, amount, category

    I want to be able to summarize the data in this table into a month by month table grouped by category. See the data table and the ideal summary table below. (If the formatting doesn't work out on this post, I am attaching a file showing these tables)

    The formulas in the Summary Table would sum the amounts in row C of the Data Table if the month in row one of the Summary Table matched the month in column A of the Data Table AND the category in column 1 of the Summary Table matched the category in column D of the Data Table.

    My tendency is to use a SUMIF function, but I suspect that an array function would be more effective. Any help?

    Something like this:
    Data Table:
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>1</td><td align=right>1/15/04</td><td>Woody</td><td align=right>125.06</td><td>Services</td><td align=center>2</td><td align=right>1/28/04</td><td>NTCS, Inc.</td><td align=right>457.83</td><td>Materials</td><td align=center>3</td><td align=right>2/12/04</td><td>KPBF</td><td align=right> 10.99</td><td>Advertising</td><td align=center>4</td><td align=right>2/14/04</td><td>GGLP</td><td align=right>101.57</td><td>Materials</td><td align=center>5</td><td align=right>3/1/04</td><td>Sea Dog </td><td align=right> 50.00</td><td>Services</td><td align=center>6</td><td align=right>3/15/04</td><td>Woody</td><td align=right>115.23</td><td>Services</td></table>
    Summary Table:
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><tr><td align=center>1</td><td align=right>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUMIF or array on two criterion (XP)

    A pivot table is the ideal solution. I have attached your workbook with a pivot table added. Here is how to create it:

    1. Click in cell A3.
    2. Select Data | PivotTable and PivotChart Report...
    3. The Pivto Table Wizard starts. Click Next (twice)
    4. Click Layout...
    5. Drag Category to the Row area, Date to the column area and Amount to the data area.
    6. Click OK to return to the Wizard.
    7. Click "Existing worksheet" and point to where the upper left corner of the summary table should be.
    8. Click Finish.
    9. The pivot table appears, but the dates aren't grouped yet.
    10. Right click the Date "button".
    11. Select Group and Outline | Group...
    12. Select Months and click OK.

  3. #3
    New Lounger
    Join Date
    Dec 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF or array on two criterion (XP)

    Great thought. Thanks!

Posting Permissions

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