Emma, of Phoenix asks, "I am a member of a ladies group that plays cards each week. We collect money after the total costs for the current month are tallied. Is there a way I can setup an Excel spreadsheet to find the cost per person?"
There are a few ways this could be handled. I'm going to use a combination of some basic formulas with the "=COUNTA" function.
Follow these steps to use the "=COUNTA" formula in conjunction with the "=SUM" formula to add up the costs of the supplies.
First, let's discuss how the "=COUNTA" function calculates figures. The "=COUNTA" function only counts cells with values. Let's use an example with Column A containing names of attendees. Cell B2 could be the formula that calculates the total number of attendees.
NOTE: Column A may change depending on the tallying or the date of the event. Therefore, you'll want to use a generic formula with the =COUNTA function.
Place the following formula in Cell B2. =COUNTA(A:A) Using the generic summation formula of "A:A" tells Excel to calculate everything in Column A. This way you do not need to change the formula as the group gets larger.
There is no need to setup the formula with A2:A15 when there are now 25 people in the group, for example.
HINT: The =COUNTA function only counts cells with contents; it does not count blank cells. Now that Cell B2 contains the number of attendees; you'll need to setup a column containing the supplies and costs associated with them.
Let's continue with placing the "Supply Items" in Column C and the "Costs" in Column D. The spreadsheet will look something like the image here.
Place a "Total" formula in the last cell below the item costs: say in Cell D12.
HINT: If you have varying items each month, you could setup the total formula as a generic column formula just as we did for Column A.
Place the following formula in Cell C3. =SUM(D12/B2)
Remember that D12 contains the total of the costs and B2 contains the total number of people. So, this formula is dividing the costs by the people which calculates a "Cost Per Person."
Use the =COUNTA function for any other situations where there is a need to count a list.
Comments