Concatenate with a comma based on a different cell value (between date range)

[LeSigNagE]

Hello!

Ive searched high and low for the solution but im truly stumped.

I have 2 tables.

1 detailing project information (name, date project was started, completed, etc).
1 showing how many projects were raised, completed, etc, each month.

I've got a countif to give the number of projects raised each month, working off a date range defined by 2 cells (first day of the month, and the first day of next month) but I'd also like to add a list of those specific project names. I think im pretty close but my formula is giving me a blank cell and i cant figure out why:

=TEXTJOIN(", ",TRUE,IF(AND(G2:G10000>=B2,G2:G10000<B3),C2:C10000,""))
=TEXTJOIN(", ",TRUE,IF(AND(**PROJECT START DATES**>=**FIRST DAY OF THE MONTH**,**PROJECT START DATES**<**FIRST DAY OF NEXT MONTH*),**PROJECT NAMES**,""))

Any help would be fantastic, thank you!

 

[FormR]

Hi, you can't use the AND() function like that in array formulas - an alternative is to nest IF() functions - like this.

=TEXTJOIN(", ",TRUE,IF(G2:G10000>=B2,IF(G2:G10000<B3,C2:C10000,""),""))

Which needs committing with CTRL+SHIFT+ENTER

Or if you have a newer version of Excel you can also try this normally entered formula.

=TEXTJOIN(", ",TRUE,FILTER(C2:C10000,(G2:G10000>=B2)*(G2:G10000<B3)))

 

[LeSigNagE]

Hello and thank you for your swift reply! This worked a treat. The second formula didnt work so I went with the first.

A few questions if you dont mind:
1) why does IF work in this instance but AND doesnt?
2) Ive never seen a formula needing ctrl/shift/enter. I had a google but cant seem to find anything to explain it - please could you give me the skinny on it?

Again, thank you for your help!!

 

[FormR]

2) Ive never seen a formula needing ctrl/shift/enter. I had a google but cant seem to find anything to

Try searching for "Excel Array Formulas"

1) why does IF work in this instance but AND doesnt?

AND() doesn't return an array, it effectively tests if all of the rows meet the condition and returns a single TRUE or FALSE.