Count/Sum Formula - Multiple Pages - Using first and last names

[JerLon]

I have a spreadsheet where I track student behavioral issues in school.

In one sheet ('Suspensions'), I track student suspensions. In that sheet, I have columns for Last Name (B), First Name (C), Days ISS (N), Days OSS (O).

In another sheet ('Student Tracker'), I track records of the number of incidents students have had in separate categories. First Name (A) and Last Name (B) I have two columns 'Days ISS' 'Days OSS'.

I want sum the total number of days a first name and last name match has been assigned OSS or ISS. So, if a student is listed eight or nine different times in the 'Suspensions' sheet with various mixes of days iss and days oss, it would add up all of those days and total them for me in the student tracker.

Any suggestions?

[Fotis1991]

Looks like to need SUMIFS.

Can you upload a small sample workbook?

[JerLon]

Tracker for Upload.xlsxI deleted everything that wasn't relevant and all student identifiers. I think I still left enough information to make the formula.

[JerLon]

Tracker for Upload.xlsxAttachment 184699I deleted everything that wasn't relevant and all student identifiers. I think I still left enough information to make the formula.

[Fotis1991]

Unfortunately i get errors in your second sheet and i have an empty first sheet. So, sorry but no sense for me.

Maybe someone else?

[JerLon]

The errors on the second page are because i deleted the page that corresponded to their formula for privacy reasons and because they aren't relevant to this formula.

I left one entry on the first page with a first, last, ISS, and OSS data entered. Those are all of the columns where data is required.

I have all of the other formulas set, just looking for help on the ISS and OSS columns on the second page.

[JerLon]

In theory, this is the formula I want but Excel is telling me I have too many functions:

=SUMIF('Suspensions'!B:B,B2,'School Incidents'!C:C,A2, [‘Suspensions’!N:N])

So, if last name in Suspensions equals last name in School Incidents and First name in Suspensions equals first name in school incidents then SUM the ISS column in Suspensions.

I would then use the same formula in the OSS column but change N:N to O:O.

But, again, too many functions??

[Fotis1991]

As i see the formula, try sumifs. Something like this.

=SUMIFs([‘Suspensions’!N:N],'Suspensions'!B:B,B2,'School Incidents'!C:C,A2)

[JerLon]

Yup, you were spot on. I played around a bit and ended up with this:

=SUMIFS(Suspensions!O:O,Suspensions!B:B,'Student Tracker'!B:B,Suspensions!C:C,'Student Tracker'!A:A)

I think I referred to the pages as the wrong names in my previous post but we got the idea.

Thanks, Much!

Making my life much easier.

[Fotis1991]

You are welcome!