Solved

Help With Excel Formula

Posted on 2015-01-20
12
71 Views
Last Modified: 2015-01-22
I am trying to calculate 2 named ranges and it is giving me a fit so I was hoping someone could help.

On sheet 1 I have a named range called StudentClass. (Nursery, 1st - 2nd Grade, etc.)
On sheet 2 I have a named range called SeptemberAttendace. (P,E,U,N)

On sheet 3 I am trying to count the September Attendance as follows
=COUNTIFS(SeptemberAttendance[6],"P",StudentClass,"Nursery")

But I don't get a value and it should be 2.
0
Comment
Question by:Mark Wood
  • 6
  • 3
  • 3
12 Comments
 
LVL 7

Expert Comment

by:slubek
ID: 40560696
Can you provide us an attachment with sample data?
0
 
LVL 2

Author Comment

by:Mark Wood
ID: 40560730
Attached is the file.

The monthly report tab is where I am trying to get the calculation
Student-attendance-record.xlsx
0
 
LVL 7

Expert Comment

by:slubek
ID: 40560821
Well, I'm not sure if I understand you correctly, but student N-001 has four attendances ('p' in 6th, 13th, 20th and 27th) in September. And I see the proper value ('4') in September!$AL$7, not '2'. And there is also value '4' in [Student Attendance Report]!$AK$16. It looks OK, doesn't it?

PS. I'm using Excel Online service on OneDrive.
0
 
LVL 2

Author Comment

by:Mark Wood
ID: 40560846
I am counting each week separately on the Monthly Reports Tab therefore it would have to be something like
 =COUNTIFS(SeptemberAttendance[6],"P",StudentClass,"Nursery").

That way it would count 1 each week for that student
0
 
LVL 7

Expert Comment

by:slubek
ID: 40560861
I still don't see that formula. Maybe its because of web interface I'm using.
I'll check it on desktop Excel tomorrow.
0
 
LVL 2

Expert Comment

by:justin_alvarez
ID: 40561039
A small note about Countifs:

They do require that the criteria ranges match in size. Your first Criteria (tab "September") contains 76 rows, but the second criteria (which I modified to pull from the first tab titled "Student List") had over 100. I trimmed the "Student List" down to 76 rows and used the following Countifs formula (slightly modified from what you originally posted)

=COUNTIFS(SeptemberAttendance[6],"P",StudentList[Class],"Nursery")

and I was able to get the number 2. I also tested it by changing the number of times "Nursery" appears. It seems to work.

Give it a look and let us know how it goes.

Best Regards
Student-attendance-record-Modified.xlsx
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Author Comment

by:Mark Wood
ID: 40561191
that is cool but I have 104 students and that can grow at any time
0
 
LVL 2

Accepted Solution

by:
justin_alvarez earned 500 total points
ID: 40561204
I think the major hurdle for the formula was to make sure that the size of each criteria range is consistent. I removed some rows to make it work, but you should be fine with expanding the table--as long as you keep the number of rows consistent.

Oh and I just realized that your COUNTIFS formula should work "as-is" (no need to adopt my modifications).
0
 
LVL 2

Author Comment

by:Mark Wood
ID: 40561258
cool. 1 other small thing if you can look at it
it doesn't count all of the adults, teachers, asst. teachers or the staff at the bottom of the list.

everything else works just by adding blank lines to the bottom of sept.
0
 
LVL 2

Author Comment

by:Mark Wood
ID: 40561272
keep in mind that the student list I have at 177 rows and September is at 122.

the report works fine except for what I mentioned above. it appears as though rows 90 and below are not being checked on the student list.
0
 
LVL 2

Author Closing Comment

by:Mark Wood
ID: 40565575
Thanks for all your help
0
 
LVL 2

Expert Comment

by:justin_alvarez
ID: 40565603
mwood6275,

I think I found why this is happening. It appears to be the Criteria "p" in the COUNTIFS function. If we look at column "I" in the "September" tab, we can see that the letters switch from "p" to "u" upon the 8th row in the column (if you are looking strictly at the row numbers in the margin, this occurs on row 14).

It appears that the COUNTIFS Function is ignoring anything in that column that does not match the letter "p". It is only counting what we want if BOTH criteria 1 and 2 are met. Because the 8th row in the September tab does not match the criteria, it will not properly count that record. As you can see below, I entered "Nursery" for the class and it still only counted 1 instance.

I'll see if there's a simple workaround. We might have to look at something a bit smarter than COUNTIFS.

September Tab
Two "Nursery" tags in the Student List
Monthly Report Tab shows only 1 "Nursery" student
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now