+ Reply to Thread
Results 1 to 15 of 15

Conditional Adding

  1. 02-04-2011, 05:42 PM #1
    Lift Off
    Lift Off is offline
    Registered User
    Join Date
    04-13-2004
    Posts
    74

    Conditional Adding

    I would like to group column A handling apples together, and bananas together etc. The number of rows of apples and bananas etc. is variable as is the selection of what is in column A. Can have any number or type of each.

    If there is a value in column C then want to total column C for apples and B for apples. So in the example apples would be B=0 and C=1. Totals for bananas would be B=2 and C=1. Since there is nothing in column C for carrots they would be ignored, or B=0 and C=0.

    How can I write the formula that only totals things if there is an amount in C for each group of what is in A?

    A B C
    apple 0 1
    apple 0 0
    apple 0 0
    banana 1 0
    banana 0 0
    banana 1 1
    carrot 0 0
    carrot 0 0

    Results
    apple 0 1
    banana 2 1
    carrot 0 0

    Thanks, Cliff
    Last edited by Lift Off; 02-09-2011 at 06:13 PM.
    Reply With QuoteRegister To Reply
  2. 02-04-2011, 06:02 PM #2
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,317

    Re: Conditional Adding

    Have you tried a pivot table? It will do exactly as you wish.
    Attached Files Attached Files
    HTH
    Regards, Jeff
    Reply With QuoteRegister To Reply
  3. 02-04-2011, 06:02 PM #3
    nimrod
    nimrod is offline
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Conditional Adding

    =SUMIF(A:A,"apple",B:B)
    Reply With QuoteRegister To Reply
  4. 02-04-2011, 06:19 PM #4
    Lift Off
    Lift Off is offline
    Registered User
    Join Date
    04-13-2004
    Posts
    74

    Re: Conditional Adding

    Jeff, I'm embarrassed, you are correct. Actually, was going to use it in a Pivot eventually. Thanks

    Nimrod, the formula won't only test apples. How would it test for bananas? etc.
    Reply With QuoteRegister To Reply
  5. 02-04-2011, 06:26 PM #5
    Lift Off
    Lift Off is offline
    Registered User
    Join Date
    04-13-2004
    Posts
    74

    Re: Conditional Adding

    Jeff, Actually Pivot does not work. I probably should have included the example a value in B but not in C. In which case I want to disregard what is in B for the group.

    Example:
    A B C
    Grapes 1 0
    Grapes 0 0

    Result:
    Grapes 0 0

    Only want to include totals if there is a value in C, by group. So if a value is in C, add what's in C and total what's in B, for the group.

    Thanks, Cliff
    Reply With QuoteRegister To Reply
  6. 02-04-2011, 07:12 PM #6
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,317

    Re: Conditional Adding

    Hi Cliff,

    To get the best result possible, you should attach a sample workbook with a small representative sample of your data and the results you desire.
    Reply With QuoteRegister To Reply
  7. 02-05-2011, 07:26 PM #7
    nimrod
    nimrod is offline
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Conditional Adding

    IDEA (1)
    Sort the column by Type and then use excels "Subtotal" feature/tool to create a subtotal by each change in column

    IDEA (2)
    Construct a pivot table

    IDEA (3)
    create a list of each unique type using Adavanced Filter... Unique and then use sumif formula

    SUGGESTION: Attach a sample file to post so we can see what you're attempting to do.
    Reply With QuoteRegister To Reply
  8. 02-06-2011, 02:03 PM #8
    Lift Off
    Lift Off is offline
    Registered User
    Join Date
    04-13-2004
    Posts
    74

    Re: Conditional Adding

    Attached is a file that indicates the data set and output required. I'd prefer the end result to be in pivot form, but would except just a table if getting there was straight forward.

    A pivot of the data set gets me most of the way there. I just don't know how to get it to not include items that do not have a value in column C.

    Thanks, Cliff
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 02-06-2011, 02:32 PM #9
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Conditional Adding

    Using the example you've posted you might get away with using a calculated field for "B", eg: = B * MAX(C)
    Attached Files Attached Files
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  10. 02-06-2011, 04:44 PM #10
    Lift Off
    Lift Off is offline
    Registered User
    Join Date
    04-13-2004
    Posts
    74

    Re: Conditional Adding

    That may work, although I need to understand what's happening. Need to understand what 'Max' is doing, but it worked in the Pivot.

    Thanks for the feedback. Cliff
    Reply With QuoteRegister To Reply
  11. 02-06-2011, 06:56 PM #11
    nimrod
    nimrod is offline
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Conditional Adding

    Quote Originally Posted by Lift Off
    I just don't know how to get it to not include items that do not have a value in column C.
    Hello Lift Off:

    You can Filter based on "C" by placing "C" in your "Page" area ( ver 2003) or "Report Filter" area (ver 2007 ) of the Pivot Table Design. Have you tried this approach ?
    Last edited by DonkeyOte; 02-07-2011 at 07:07 AM. Reason: corrected tags
    Reply With QuoteRegister To Reply
  12. 02-07-2011, 07:07 AM #12
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Conditional Adding

    @nimrod, the 1 in C need only appear in one of n transactions for a given type rather than all (see "banana" ex. in post # 1) - a report filter would not suffice (in present form).
    Reply With QuoteRegister To Reply
  13. 02-09-2011, 03:10 PM #13
    Lift Off
    Lift Off is offline
    Registered User
    Join Date
    04-13-2004
    Posts
    74

    Re: Conditional Adding

    Hi,

    I've attached a file of sample data and a 'normal' pivot that results from the data, plus a sample of the way the data should look. Again, the pivot should group as it normally does but only include groups which have an amount in column C of the data set. "Blank" means it doesn't show in the pivot.

    Note the total of column C is correct at 16 but B goes from 17 to 5 on the desired output.

    Thanks again for the help. Cliff
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  14. 02-09-2011, 03:18 PM #14
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Conditional Adding

    Based on the sample ...

    With the Pivot active go to Options Tab on Ribbon:

    Click Fields, Items & Sets (XL2010) or Formulas (XL2007) -> Calculated Field

    Set Name as: <space>B
    (obviously <space> means tap space)

    Set Formula as: = B * SIGN(MAX(C))

    Click Add click OK

    Now modify your Pivot - remove "B" from the Pivot and move the new " B" field to it's position.

    If you then want to filter out the 0 C Items you can apply a Values Filter to A where Sum of C > 0

    Does that help ?
    Reply With QuoteRegister To Reply
  15. 02-09-2011, 06:12 PM #15
    Lift Off
    Lift Off is offline
    Registered User
    Join Date
    04-13-2004
    Posts
    74

    Re: Conditional Adding

    Yes!!!

    That does more than help. It does the job!

    Can't thank you enough for pointing me to the solution.

    Best, Cliff
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1