Lookup and sum if multiple criteria are met

bh24524

Active Member
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007
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
Joined
Oct 24, 2012
Messages
5,088
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007
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:

1623863426592.png


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

1623863478262.png

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

1623863580414.png


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

1623863666461.png


and look at sheet1 again for the results:
1623863726938.png


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:
1623863838278.png


Sheet2:
1623863859689.png


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
Joined
Oct 24, 2012
Messages
5,088
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007

ADVERTISEMENT

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

Sheet1 Unsorted with the two new names added:
1623867756903.png

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:
1623867367536.png


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

Sheet1
1623867600956.png


Sheet2
1623867565943.png


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
    1623867310489.png
    19 KB · Views: 3
  • 1623867545860.png
    1623867545860.png
    11.3 KB · Views: 2

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,088
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Aug 23, 2010
Messages
16,907
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top