Solved

Help With Excel Formula

Posted on 2015-01-20
12
73 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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 on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

770 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