Excel: Use an array formula to calculate monthly attendance in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, I have 2 columns. One column with "XXXXXX" which indicates a person's attendance that day and a second column indicating how many hours he was there that day. I want to compute the total hours that a person was there for a month.

How can I do this?

Answer: You can do this with an array formula.

Let's take a look at an example.

In cell E6, we want to display the total hours for Cornell. This is calculated as the sum of column L where the corresponding value in column E contains "XXXXXX". To do this, we've created the following array formula:

=SUM((E2:E5="XXXXXX")*($L$2:$L$5))

When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=SUM((E2:E5="XXXXXX")*($L$2:$L$5))}

Next, to get the total hours for Cooper, we've created the following array formula in cell F6:

{=SUM((F2:F5="XXXXXX")*($L$2:$L$5))}

And to get the total hours for Manuel, we've created the following array formula in cell G6:

{=SUM((G2:G5="XXXXXX")*($L$2:$L$5))}

And so on...