+ Reply to Thread
Results 1 to 7 of 7

Add Total of Several Formulas

  1. #1
    Registered User
    Join Date
    09-02-2008
    Location
    Orlando
    Posts
    18

    Add Total of Several Formulas

    I am trying to figure out which formula can do the following:

    In Cells A5:A50, look for "292"
    In Cells C5:C50, look for "Y"

    If either have both add 1, so that if 7 different times we found agent 292 had Y, the total would be 7.

    Would also like to know how to add it if I needed to add another cell, with numbers or text.

    Thanks for your help,
    Jennifer

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: Several Forumulas, Add Total

    If you're using Excel 2003:

    =SUMPRODUCT(--(A5:A50=292),--(C5:C50="Y"))

    If you are using Excel 2007/2010:

    =COUNTIFS(A5:A50,292,C5:C50,"Y")

    The latter is preferred if you are using 2007/2010

    Regards

  3. #3
    Registered User
    Join Date
    09-02-2008
    Location
    Orlando
    Posts
    18

    Re: Add Total of Several Formulas

    Thanks for your help. And what if I wanted to pull from several different columns? Such as,
    if cells a5:a50, b5:50, and r5:r50 equal 292 & cells g5:g50, m5:m50, t5:t50 equal "y"? I've tried to add multiple areas to search, but I'm missing something. It doesn't locate any of them.

    Thanks,
    Jennifer

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

    Re: Add Total of Several Formulas

    Do you mean all columns must meet the criteria simultaneously (ie an AND test) or only one of the aforementioned columns in each case (an OR test) ?

    AND based test

    Please Login or Register  to view this content.
    OR based test

    Please Login or Register  to view this content.
    If you are using XL2007 you can replicate the first AND example in COUNTIFS, you can not however replicate the remaining examples
    (ORs can not be conducted via a single COUNTIF(S))

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: Add Total of Several Formulas

    @DonkeyOte; Please can you explain what the tilda (~) does in the second AND formula and what does SIGN do in the OR formula.

    Thanks

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

    Re: Add Total of Several Formulas

    @TMSchucks - didn't think you were on line sorry - I wouldn't have chimed in !

    Quote Originally Posted by TMS
    Please can you explain what the tilda (~) does in the second AND formula
    Tilde - in this context used purely as a delimiter between the values.

    Should G5 contain YY, M5 blank and T5 Y testing G5&M5&T5 to YYY would return a false positive ... by using an appropriate delimiter you negate that, ie: YY~~Y <> Y~Y~Y

    Quote Originally Posted by TMS
    what does the SIGN do in the OR formula
    Please Login or Register  to view this content.
    is just an alternative to

    Please Login or Register  to view this content.
    ie the SIGN of any number > 0 is 1, SIGN of 0 being 0 ... given we can't have a negative result in this context there is no issue of a -1 multiplier

    some argue that the additional Function call makes it less desirable than the latter approach
    for me, however, having debugged a few SUMPRODUCTs I would say SIGN is far easier to read through
    If the overhead of using SIGN is significant enough to be a concern then SUMPRODUCT should not be being used in the first instance !

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: Add Total of Several Formulas

    @DonkeyOte: thanks for the clarification.

    Feel free to play all the music you like ... way beyond my expertise!

    As much as I enjoy being able to answer questions, it's great to learn new techniques and approaches.

    Thanks

+ 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