Results 1 to 3 of 3

Thread: Condensing Formulas

  1. #1

    Condensing Formulas

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello! I have a quick and probably easy question... I have multiple COUNTIFS, but the only thing that they are changing is the end checking if it contains a certain name (the data may be presented with "Poplar" or "Poplar Hall", for example).

    The data pulls to a different worksheet setup with Date and Number (where the formula is located).

    Here's what I have currently, where (in this case) A3 = 1/16/13 or 41290:

    =COUNTIFS(Table1[Date Value],A3,Table1[Building],"*alder*")+COUNTIFS(Table1[Date Value],A3,Table1[Building],"*elm*")+COUNTIFS(Table1[Date Value],A3,Table1[Building],"*poplar*")
    This returns 76, which is the correct result I am looking for.

    I tried condensing using curly brackets to {"*alder*","*elm*","*poplar*"}, but I am not sure that I fully understand their functionality, and it was only returning 32 as the answer.

    Attached is sample data for which the result should be 76.

    Thanks so much for your help!

    Sample Data.xlsx

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Mississauga, Canada
    Excel Version
    Excel 2016

    =SUMPRODUCT(COUNTIFS(Table1[Date Value],C2,Table1[Building],{"*alder*","*elm*","*poplar*"}))

    The SUMPRODUCT allows each of the conditions to be checked separately, then summed together.

  3. #3
    Perfect! So much more elegant

    Thank you!

Posting Permissions

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