Solved

Help With Excel Formula

Posted on 2015-01-20
12
68 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
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 Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

759 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

20 Experts available now in Live!

Get 1:1 Help Now