Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Unanswered: Need macro to subtotal range determined on text value

    I'm not up on spreadsheets. Need a macro to subtotal a range of a column determined by a common value in a certain range of cells (all within another column). Need to use value, as cell locations will change. For example, for all instances of "x" in column C, I need to group and subtotal all the values in column F in the rows which I think I can arrange it so that the ranges

  2. #2
    Join Date
    Sep 2003
    Location
    Cincinnati, Oh USA
    Posts
    203

    Re: Need macro to subtotal range determined on text value

    It sounds like you might need to use a conditional "sumif" function for array. Can you subit an example?

    Originally posted by david-dsi
    I'm not up on spreadsheets. Need a macro to subtotal a range of a column determined by a common value in a certain range of cells (all within another column). Need to use value, as cell locations will change. For example, for all instances of "x" in column C, I need to group and subtotal all the values in column F in the rows which I think I can arrange it so that the ranges
    Rick Knight
    KnightShift Office Solutions and Horse Breaking
    VB, VBA, FileMaker, Access Solutions, Web Solutions

  3. #3
    Join Date
    Dec 2003
    Posts
    3

    Re: Need macro to subtotal range determined on text value

    Don't know if that would do it. I have a spreadsheet which records oversize prints for billing purposes, sequentially. I can sort the column that records the client, then the project. What I need is a macro to insert a row after each project, then subtotal the number column for each section. When you get down to it, inserting a row based on a new text value going down a column is all I really need.
    Originally posted by RickKnight
    It sounds like you might need to use a conditional "sumif" function for array. Can you subit an example?

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    I think Rick might be correct on using SUMIF. Take a look at this (picture in Word file). The subtotals are off to the right. Is it necessary to have an extra row between? If separation visually is needed, why not use Conditional Formatting?
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Dec 2003
    Posts
    3

    Can I use conditional sum

    When I don't know the range? It's going to vary each month. I guess I will know the columns to add up. Also, I need to exclude ranges based on whether they are billable (another column).

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, two separate issues. If you know the columns, then it won't matter if you know how many items are added. The SUMIF will take care of that.

    The other issue involves two (or more) conditions. In this case, you want to use an array formula (instead of ENTER, use CTRL + SHIFT + ENTER).

    Let's assume Column A has the Client, Column B Billable with either "Yes" or "No" (without quotes), and Column C has the amount. Also, assume that Row 1 has headers. Also, put the list of clients in Column F beginning at F2.

    Then, put this formula in cell E2

    =SUM(($A$2:$A$65000=F2)*($B$2:$B65000="Yes")*($C$2 :$C65000))

    After you put it in, be sure to array-enter it (CTRL + SHIFT + ENTER).

    Note that with array formulas, sometimes use $A:$A can not work, so that is why I put 65000 (any number up to 65536 will work).
    Last edited by shades; 12-15-03 at 18:51.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    Here is an attachment that will show you how I set it up.
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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