CONCATENATE with multiple IFS

[mrnarley]

Hello,

The formula below works great for counting. I need to use the same formula to CONCATENATE all of the names in B:B. It seems as though I should just replace COUNTIFS with CONCATENATE and change Data!C:C to Data!B:B, but it doesn't work. I tried a search on the web using the title of this thread but came up with very few clues. Is it even possible to CONCATENATE with multiples IFS? Thanks!

=COUNTIFS(Data!C:C,IF(B2="All","*",B2),Data!D:D,IF(B3="All","<4",B3),Data!E:E,">="&W5,Data!E:E,"<="&X5)

 

[mikerickson]

The more I think about it, the first argument in the above formula should be B:B

 

[mrnarley]

Excel suggested that I remove the 3rd parenthesis, which I did. The “All” portion seems to be straightened out now and it is not including the column label in the results. Thanks! New problem though, or at least I think it’s new.

The function is returning results from 1 row below the records in my data spreadsheet that I’m looking for. For instance, it should be returning the Project Name from row 50 based on my criteria selections on my worksheet, but it’s returning the results from row 51. That is one of the strangest things I’ve ever seen. There’s nothing in the function string (that I can see) that’s telling it to do that. I have visually verified the integrity of my data spreadsheet. Why would it offset the results by one row? Is it in the code somewhere?

 

[mrnarley]

The more I think about it, the first argument in the above formula should be B:B

Oops! I left my browser open on the 2nd page and didn't see your last post when I came back. You're right, I didn't notice that "B2" was the arguement instead of B:B. It now reads:

=Concat5Ifs(Data!B:B,", ",Data!E:E,">"&V5,Data!E:E,"<"&W5,Data!A:A,IF(B1="All","<>",B1),Data!C:C,IF(B2="All","<>",B2),Data!D:D,IF(B3="All","<>",B3))

And it works perfectly now!

Mike, thanks so much for all of your help. Awesome!

 

[mikerickson]

Glad to help.