+ Reply to Thread
Results 1 to 8 of 8

Conditional summing of cell with multiple conditions

  1. #1
    Registered User
    Join Date
    07-01-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Conditional summing of cell with multiple conditions

    I have a row of text that look like this:

    |cat F | dog | dog | cat F | cat M | cat M | dog | dog |

    So either the cell is 'cat F', 'cat M' or 'dog'. They are grouped in pairs (hence the color coding).

    What I want to do is, for each pair, find all those that are 'cat' in the 1st column (doesn't matter if 'F' or 'M'). Then for those that are cat, find which ones have 'dog' in the 2nd column. In the example above, there are 2 pairs that have cat in the 1st column....and out of that, only 1 has 'dog' in the 2nd column. And then dividing the 1 match with the 2 pairs, the result should give .5'
    I prefer if there was a function that could use one cell to do it (even if nested), but if it has to to split up in 2 steps, that's ok.

    This is what I have tried (to first find the match of 'cat' 'dog' pair):
    =SUMPRODUCT(((MOD(COLUMN(A1:A8),2)=0)*(A1:A8="cat*"))*(MOD(COLUMN(A1:A8),2)=1)*(A1:A8="dog"))

    doesn't work.

    and SUMIF seems to take only 1 conditions.

    If I break it up into steps, I can start finding the pairs with cat as the 1st column:
    =SUMPRODUCT((MOD(COLUMN(A1:A8),2)=0)*(A1:A8="cat*"))
    which works, but only gives me a number (2). This doesn't help because I need to know which 2 pairs they are to continue on with the calculations.
    Last edited by x12179x; 07-02-2009 at 12:46 AM.

  2. #2
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Conditional summing of cell with multiple conditions

    A sample file in excel will be helpful for others to understand the problem exactly !!!!
    If this post helps, Please don't 4get to click the star icon located at the bottom left of my Post.

  3. #3
    Forum Contributor mewingkitty's Avatar
    Join Date
    09-29-2008
    Location
    Fort McMurray, Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    949

    Re: Conditional summing of cell with multiple conditions

    Eh
    First off, your example doesn't really show sets of pairs, it just shows eight things listed in a row.

    Check out the attached worksheet and see if it sorta outlines what you're going for. Might give you an idea of how to proceed.

    Counts those pairs which have cat in the first pairing,
    counts the pairs which have cat in the first and dog in the second
    divides the second by the first.

    Left it broken into steps, since "IF" statements are a little wonky to break down when they're mashed together into one formula right off the bat.

    mew!
    Attached Files Attached Files
    =IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Conditional summing of cell with multiple conditions

    Your data is seemingly in a column as opposed to a row ... mixed terminology will invariably confuse.

    I think perhaps the below does what you want:

    Please Login or Register  to view this content.
    EDIT:

    Whenever I find I'm using 2 SUMPRODUCTs to create an Average I invariably think about using an Average Array as it can be simpler to piece together - shorter to write... on that basis perhaps also:

    Please Login or Register  to view this content.
    Last edited by DonkeyOte; 07-02-2009 at 03:31 AM.

  5. #5
    Registered User
    Join Date
    07-03-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Conditional summing of cell with multiple conditions

    First: There seems to be a confusion between columns and rows in your question. I will assume that in fact, the data are arranged along the first row in exactly the manner you have given. (In other words, the 'cats' and 'dogs' are found in cells A1, B1, C1, D1 etc. and nowhere else)

    Now, the only sensible response to your query is to question the requirement that it should be done using one cell.

    Alternatively, if it is to be done using one cell, then the best method is to write a custom VBA function:

    Please Login or Register  to view this content.
    Now the formula:
    =DoggyCatCat(A1:H1)
    will return 0.5.

  6. #6
    Registered User
    Join Date
    07-01-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Conditional summing of cell with multiple conditions

    Yeah, I think I mixed up rows and columns. I attached a file to give an idea of my problem.

    Thanks Ancalagon12321, that actually worked! But unfortunately, I'm not the only one who will use the excel sheet, so a function entered into cell(s) would probably be more familiar if they wanted to edit anything.

    mewingkitty, that does work for the example I gave...but my example is very simplified. It's not just 1 row of data I have, and there are more than 4 'pairs' in each row. The file attached gives a better idea (but even that is simplified!). There are many ore rows, each going on for many more columns. So using that many cells for calculation would easily clutter the sheet.


    I was able to calculate the total # of pairs with "cat" in the 1st 'pair' (shown in column A). However, I haven't been able to find a way to calculate those that had "cat" as pair 1 and "dog" as the 2nd pair.

    DonkeyOte , yeah, I meant the data to be in the same row (like in
    mewingkitty's file), not column. The function didn't work for my file, and that might be why. I didn't have much knowledge to edit it to fit the way my data was arranged though.

    What is "--" and "LEFT"? and what does it do?
    Attached Files Attached Files

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Conditional summing of cell with multiple conditions

    Based on your latest file and your original request to find the AVERAGE number of pairs of cat/dog versus pairs cat/anything:

    B2: =AVERAGE(IF((MOD(COLUMN($D2:$R2),2)=0)*(LEFT($D2:$R2,4)="cat "),0+($E2:$S2="dog")))
    committed with CTRL + SHIFT + ENTER
    copied down as required

    re: -- ... double unary operator - used to coerce boolean output (TRUE/FALSE) to numerical equivalent (1/0 respectively) - for more info. on SUMPRODUCT & Coercion see the SUMPRODUCT link in my sig to Bob Phillips' white paper.

    re: LEFT ... checks x characters in from the left in a given string... see XL Help for more info.

  8. #8
    Registered User
    Join Date
    07-01-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Conditional summing of cell with multiple conditions

    Thanks DonkeyOte! That works.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1