Condensing Formulas

[ihenry]

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

[NBVC]

Try:

=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.

[ihenry]

Perfect! So much more elegant

Thank you!