Solved

Help With Excel Formula

Posted on 2015-01-20
12
76 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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 the scrolling table in Microsoft Excel using the INDEX function.

627 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