# Excel 2007 : Trouble with Countifs

1. ## Trouble with Countifs

I am attempting to give credit to a person if they worked on a report. 1 point per report worked on. I have tabs for the reports per quarter and a talley tab. The report sheet for the 1st quarter (FY 12 - 1st Q), E Column has complete or incomplete, G Column has their student number in it, but can have more than one student number in it. (I do not want to merge multiple cells and split the two numbers apart as I am already doing that somewhere else in the sheet). On the talley sheet, (FY 12 - Stu Tot) I have their names, (Column A), next to their student number (Column B). Is it possibe to get Column C - (1st Quarter) of (FY 12 - Stu Tot) to talley the total number of times it sees the student number. I have gotten this far but don't want to have to keep retyping the student numbers:
=countifs('FY 12 - 1st Q!G3:G1000, "*1458*", 'FY 12 - 1st Q!E3:E1000,"complete")
Since 1458 is in Column B of the Stud Tot sheet is there a way to make that work without retyping every student number.

I abbreviated for shortness (i.e. student numbers are much longer than 4 digits)

2. ## Re: Trouble with Countifs

You can reference the student number in a cell like so:

=countifs('FY 12 - 1st Q!\$G\$3:\$G\$1000, "*" & B2 & "*", 'FY 12 - 1st Q!\$E\$3:\$E\$1000,"complete")

Where B2 contains the student number, and you need to make the ranges absolute with the \$ signs in order to "freeze" them. Then you can copy down the formula.

3. ## Re: Trouble with Countifs

Try this version in C2 copied down

=COUNTIFS('FY 12 - 1st Q!G\$3:G\$1000, "*"&B2&"*", 'FY 12 - 1st Q!E\$3:E\$1000,"complete")

I'm assuming that all student numbers are 4 digits are they, no possibility of having one student number as a "substring" of another?

4. ## Re: Trouble with Countifs

Originally Posted by NBVC
You can reference the student number in a cell like so:

=countifs('FY 12 - 1st Q!\$G\$3:\$G\$1000, "*" & B2 & "*", 'FY 12 - 1st Q!\$E\$3:\$E\$1000,"complete")

Where B2 contains the student number, and you need to make the ranges absolute with the \$ signs in order to "freeze" them. Then you can copy down the formula.
Thank you very much! This worked perfectly, and reminded me of the whole "freeze" aspect.

There are currently 1 users browsing this thread. (0 members and 1 guests)