Lookup and sum if multiple criteria are met

[bh24524]

I am looking for a way to add point amounts together on one tab if another tab has certain information. The relevant info is as follows:

Tab A
Column A is student name
Column B is student number
Column C is where we will do the lookup into Tab B to see if the student name/number are on that tab. (All student names and numbers will be on Tab A but only some of them will be on Tab B)

Tab B
Column A is Student name - actual names start on row 3
Column B is student number - actual numbers start on row 3
Column C is Assignment 1 - this is located in C1. C2 has a flat point amount of 50
Column D is Assignment 2 - this is located in D1. D2 has a flat point amount of 100
Column E is Assignment 3 - this is located in E1. E2 has a flat point amount of 150

For each student that is actually on Tab B, if they have completed an assignment, it will say "Complete" under each numbered Assignment column. If they haven't completed, it will just be blank.

So what I want Column C to do in Tab A is first lookup to see if the student in that particular row is also located on Tab B. If they are, it should look for any occurrences of the word "Complete" and add the corresponding amounts in C2 thru E2 if all of them say complete. So on Tab A in column C2, let's say that student does appear in Tab B and has completed assignment 1 and assignment 3. They would have 200 points showing in C2 of Tab A.

What formula could be used in a scenario like this?

 

[etaf]

does this work
=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!B2)*(Sheet2!$C3:$E3="complete")*(Sheet2!$C$2:$E$2))

will only be on dropbox a day

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Assign-Complete-Etaf.xlsx
	A	B	C
1	name	id	Score
2	a	1	150
3	b	2	0
4	c]	3	200
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!B2)*(Sheet2!$C3:$E3="complete")*(Sheet2!$C$2:$E$2))

Assign-Complete-Etaf.xlsx
	A	B	C	D	E
1	name	id	Assign1	Assign2	assingn3
2			50	100	150
3	a	1	complete	complete
4	b	2
5	c]	3	complete		complete
Sheet2

 

[bh24524]

Hi, so I thought this would work but it seems not. So in this sheet, there will constantly be names added and deleted from it. It would require re-sorting to get all names in alpha order and that seems to be messing up the formula calculations. Here is an example:

So in sheet2, we start off with three names as pictured:

Noting where they have "completes" we then go over to sheet1:

These names were all by default in alpha order and the formula added. But if we add two names for example to sheet1:

I have dragged the formulas down in advance and then we go to sheet2 to enter data:

and look at sheet1 again for the results:

Looks fine, right? everything is calculating according to Sheet2. But now here's what happens when I sort these names to integrate them in Alpha order:

Sheet1:

Sheet2:

Drew Smith has all three completes which totals 300 points but it is only giving him 150. I think maybe a different formula is needed to account for sorting but not sure what it would be. Yours was a good try though. I just hadn't thought of the sorting being an issue with it.

 

[etaf]

strange , not sure why it does it with the formula refering to sheet1!
I had the same issue with

=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!B2)*(Sheet2!$C5:$E5="complete")*(Sheet2!$C$2:$E$2))

I removed , and now it sorts OK

=SUMPRODUCT((Sheet2!$B$3:$B$11=B2)*(Sheet2!$C5:$E5="complete")*(Sheet2!$C$2:$E$2))

Does that resolve the adding and removing and sorting

 

[bh24524]

Unfortunately, it doesn't. So here's what I have:

Sheet1 Unsorted with the two new names added:

I included the formula in the screenshot that way you can see I did remove the Sheet1 that was in the initial formula

Sheet2 Unsorted with two names added:

Okay so now if I sort Sheet1 by name and then sort Sheet2 by name, I get this:

Sheet1

Sheet2

I think the problem is with the second part of the formula. As an example, Drew Smith who is now in row 6 of Sheet2 after sorting is done has a formula that still references Row 7 which seems to be why it is giving him 150 points and not 300. Looking at Anna Bell who is now in top row on each sheet, her's is referencing Row 6 in Sheet2.

 

[etaf]

not sure on why , as its an array formula , then that maybe the issue with sorting , and may need an index/match type formula maybe with a SUMIFS, which i'm not sure how to do or even VBA again not something i respond to in forums

 

[Marcelo Branco]

Try this formula in C2 of Sheet1 - worked for me before and after sorting
=IF(ISNUMBER(MATCH(B2,Sheet2!B:B,0)),SUMIF(INDEX(Sheet2!C:E,MATCH(B2,Sheet2!B:B,0),0),"Complete",Sheet2!C$2:E$2),"Not found")
copy down

M.