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.

 

[AlanY]

would you care to post a small sample of the data and desired result to help the helpers?

 

[Fluff]

This part of you formula makes no sense
COUNTA(SUMIF('Work submitted'!$A$2:$A$250,A2,'Work submitted'!$C$2:$C$250)=1)

The part in blue will return either true or false & counta will then count that as 1 regardless of the sumif.

 

[Fluff]

Cross posted COUNTA, SUMIF and Concatenate - OzGrid Free Excel/VBA Help Forum

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[Fluff]

Judging by the file on Ozgrid, is this what you want.

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

 

[El Gallo]

Judging by the file on Ozgrid, is this what you want.

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

Close. If Jimmy Carter turned his work in late, I want to put an "x" in the C column (titled "Late") of the "Work Submitted" sheet and have that "x" show up next to his point totals on the "To Be Entered" sheet. If Carter turned in his work on time, all I want is the point total to show up next to his name on the "To Be Entered" sheet.

Something like this...

If late:

James Carter

10 x

If turned in on time:

James Carter

10

 

[El Gallo]

Cross posted COUNTA, SUMIF and Concatenate - OzGrid Free Excel/VBA Help Forum

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

Will do. That makes sense and it won't happen again. Thanks.

 

[Fluff]

What should happen if he appears 3 times & only handed in the work late once?

 

[El Gallo]

What should happen if he appears 3 times & only handed in the work late once?

He won't appear three time because this will only be used for one assignment. The names come from a master list and they're to be matched up with a list of students who did turn in work (on time or late).

 

[Fluff]

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",""))