Skip a column help

[chef855]

Hi there!

I've dug around Google and I cannot get this to work at all. I'm trying to track attendance for one class that's split. So students might attend before lunch, but not after lunch or vice versa. I have my columns set as such: H2 would say 9/14 Pre and then I2 would say 9/14 Post. I'm trying to get it so that it counts only one instance. So if a student was absent for both halves it's one absence in the total, not 2. Hope this makes sense. I've tried doing a few different formulas and ideas, but nothing that seems to do what I'm looking for.

 

[chef855]

I've tried the exact formula that you have in your screen capture and don't get any errors.

Does it work correctly for you with a smaller range like the one in the example formula that I posted?

 

[jasonb75]

Select the first cell with a #VALUE! error. Press the f2 key, then press Shift Ctrl Enter together (in the same way that you would press Ctrl Alt Delete for task manager).
Does that one give the correct result now?

 

[chef855]

Select the first cell with a #VALUE! error. Press the f2 key, then press Shift Ctrl Enter together (in the same way that you would press Ctrl Alt Delete for task manager).
Does that one give the correct result now?

It removes the #value error, but it's not giving me the correct results for tardies or unexcused absences. The first row with a student is coming back with 5 for each when that row is void of any t or u's.

 

[jasonb75]

Ok, we're getting somewhere. That tells me that you either don't have office 365 (as it says on your profile), or you're a long way behind on updates (common for large corporations with sleepy IT departments).

Are there any hidden characters in the cells that appear to be empty? The formula will only pick up on cells that have something in them, so an incorrect result will either mean that there are hidden characters in the cells, or that the formula is looking at the wrong row.

 

[chef855]

Ok, we're getting somewhere. That tells me that you either don't have office 365 (as it says on your profile), or you're a long way behind on updates (common for large corporations with sleepy IT departments).

Are there any hidden characters in the cells that appear to be empty? The formula will only pick up on cells that have something in them, so an incorrect result will either mean that there are hidden characters in the cells, or that the formula is looking at the wrong row.

I was told we had 365, but I guess it's Professional Plus 2019. I checked and cleared the columns and it didn't help. I added in some dummy info in that row and it wasn't counting that either. It stayed static at 5.

 

[jasonb75]

Try clearing the formulas and re entering the first one (using Shift Ctrl Enter as before). It's possible that with the other problems encountered that the formula is out of alignment.

 

[chef855]

Try clearing the formulas and re entering the first one (using Shift Ctrl Enter as before). It's possible that with the other problems encountered that the formula is out of alignment.

Did that, but it's basically back to what I had with my =countif formula. I put in 3 t's as a test: 9/13 "pre", 9/13 "post" and 9/14 "pre". It's counting 3 tardies.

 

[chef855]

Also, perhaps I'm going about this the wrong way. I've got a pre and post but I just want it to count one. If a student is gone for one but not the other, it should still return the same result as if they were gone for both. Is there a formula that says "hey if columns with "pre" and "post" both have the same value, just count one of those"?

 

[jasonb75]

That is what the formula does. Trying to explain it in a way that might make sense, it counts H and I, then puts the count against I, counts J and K then puts the count against K and so on. Then it looks to see how many of those counts are greater than zero and counts those.

For what you want to do this is the only way that will work correctly. Anything else will count all entries without identifying Pre and Post as being the same thing.

Can you upload the workbook to dropbox and post a link to it here so I can take a look at it? I have a feeling that it is something simple that we are missing.

 

[chef855]

That is what the formula does. Trying to explain it in a way that might make sense, it counts H and I, then puts the count against I, counts J and K then puts the count against K and so on. Then it looks to see how many of those counts are greater than zero and counts those.

For what you want to do this is the only way that will work correctly. Anything else will count all entries without identifying Pre and Post as being the same thing.

Can you upload the workbook to dropbox and post a link to it here so I can take a look at it? I have a feeling that it is something simple that we are missing.

Yeah, let me fuzz some of the student data and I'll upload it.