COUNTA, SUMIF and Concatenate

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    mrexcel.com/board/threads/coun…tenate-and-sumif.1145524/

    Hi Everyone,


    I'd like to concatenate values from two columns if they match the criteria from another sheet. Here's what I've come up with so far but I just can't get it give me the correct values:


    =IF(COUNTA(SUMIF('Work submitted'!$A$2:$A$250,A2,'Work submitted'!$C$2:$C$250)=1),CONCATENATE(SUMIF('Work submitted'!$A$2:$A$250,A2,'Work submitted'!$B$2:$B$250)&" "&(SUMIF('Work submitted'!$A$2:$A$250,A2,'Work submitted'!$C$2:$C$250))),(SUMIF('Work submitted'!$A$2:$A$250,A2,'Work submitted'!$B$2:$B$250)))


    The data I want is a score and if it was late. If it wasn't late, all I want is the score.


    I've attached a dummy worksheet in the hopes that it helps to explain what I'm after.


    Help example.xlsx