COUNTA, Concatenate and SUMIF

[El Gallo]

Hello,

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.

 

[El Gallo]

That's the one! Thank you so much! Teaching from home has been rough and your help will make it a lot easier. I am so grateful. And sorry about the cross post.

 

[El Gallo]

That's the one! Thank you so much! Teaching from home has been rough and your help will make it a lot easier. I am so grateful. And sorry about the cross post.

If you don't mind, what are the principles I was missing out on?

 

[El Gallo]

Ok, how about

=CONCATENATE(SUMIF('Work submitted'!$A$2:$A$250,A2,'Work submitted'!$B$2:$B$250)&IF(COUNTIFS('Work submitted'!$A$2:$A$250,A2,'Work submitted'!$C$2:$C$250,"x")>0, " x",""))

That's the one! Thank you so much. Teaching from home has been rough and this will make it quite a bit easier. I am so grateful to you. And sorry about the cross post. What a rookie... )

If you don't mind, what were the principles I was missing out on?

 

[Fluff]

The main problem was the part in blue

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

as it will always return 1 (which is treated as true), so the false part of the IF will never get looked at.
Also the part in red is trying to sum col C, part that column only has xs in it which can't be summed.

 

[El Gallo]

Ah, I see that now. Is there a way to “sumif” for text? I tried just using the “=” but clearly that wasn’t working and google has next to nothing for that search.

 

[Fluff]

You can only sum numbers, not text.

 

[El Gallo]

You can only sum numbers, not text.

So there's no way to find text in a column that is next to a column of names?

 

[Fluff]

I'm afraid you are not making much sense.
You can find text, but you cannot sum it.

 

[El Gallo]

I want to learn the formula for finding whatever text might bin in a cell of column C only if the name is in column A... Hopefully that makes sense. :0) In the original example I wanted James Carter's grade (which would be found in a cell of column B) and whether or not it's late (which would be found in a cell of column C). But I'd like to enter other information than just the "x" and have that formula return with the other information. I tried COUNTA but that doesn't work either, for obvious reasons.

 

[Fluff]

Ok, how about

=SUM(FILTER(Table1[Total points],Table1[Name]=A2,0))&" "&TEXTJOIN(", ",,FILTER(Table1[Late],Table1[Name]=A2,""))