Solved

Help With Excel Formula

Posted on 2015-01-20
12
74 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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
 
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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.

821 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