# Lookup and sum if multiple criteria are met

#### bh24524

##### Active Member
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?

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### etaf

##### Well-known Member
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

Assign-Complete-Etaf.xlsx
ABC
1nameidScore
2a1150
3b20
4c]3200
Sheet1
Cell Formulas
RangeFormula
C2:C4C2=SUMPRODUCT((Sheet2!\$B\$3:\$B\$11=Sheet1!B2)*(Sheet2!\$C3:\$E3="complete")*(Sheet2!\$C\$2:\$E\$2))

Assign-Complete-Etaf.xlsx
ABCDE
1nameidAssign1Assign2assingn3
250100150
3a1completecomplete
4b2
5c]3completecomplete
Sheet2

#### bh24524

##### Active Member
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

##### Well-known Member
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

##### Active Member

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.

#### Attachments

• 1623867310489.png
19 KB · Views: 3
• 1623867545860.png
11.3 KB · Views: 2

#### etaf

##### Well-known Member
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

##### MrExcel MVP
Try this formula in C2 of Sheet1 - worked for me before and after sorting
copy down

M.

Replies
5
Views
216
Replies
9
Views
283
Replies
20
Views
668
Replies
13
Views
241
Replies
0
Views
77

1,147,475
Messages
5,741,347
Members
423,656
Latest member
Medrok2021

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back