Formula needs to show both blanks and zeroes.

Attachment did not come through

http://www.mrexcel.com/forum/excel-q...es-blanks.html

austin2, your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

What is the difference between these two:

leave blank if the person didn't attend and wasn't expected to,
and put a N/A if the person wasn't expected to attend meeting (i.e. hadn't joined team yet, wasn't required to attend, etc)

When Excel takes a sum of a range, blank cells and cells with non-numeric values are interpreted to be zero. When you require that a pivot table cell be a sum, it's going to have to have a number in it, even if that number is zero. My first thought is that for what you are defining, you would need to do this with formulas rather than a pivot table, and it could get a little involved.

However, there is a fundamental weakness in the way that data is recorded in the Attendance sheet. Even if you could get a pivot table to look like your desired example, a 0 would tell you they didn't go to any meetings, but it wouldn't tell you how many required meetings they missed. A better way to structure the data would be for each person to have a count of required meetings and count of meetings attended. See attached redesign. I didn't update your pivot tables because I am not sure what you are trying to get out of those that you don't already have on the Attendance sheet.

I also see no need to turn automatic calculation off. I also removed merged cells, which can cause trouble unnecessarily.Excel attendance redesign.xlsx