Condensing Formulas

ihenry

New member
Joined
Mar 24, 2014
Messages
2
Reaction score
0
Points
0
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:

Code:
=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!

View attachment Sample Data.xlsx
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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

New member
Joined
Mar 24, 2014
Messages
2
Reaction score
0
Points
0
Perfect! So much more elegant :)

Thank you!
 
Top