Excel Excel 2007 - Workbook CountA and sums help needed

[ariehc]

Hello,
I have a workbook with many worksheets. One of the worksheets is a summary page for the results from the other worksheets. One of my worksheets uses CountA to indicate the number of individuals to attend a workshop (i.e. there are six attendees who were referred -- staff enter 'y' into the cell for each attendee). The class lists are broken down by each week.

What I would like to do is have my summary worksheet show the count for the entire month for the class. In other words, I need to be able to provide the sum of each weeks counts of referrals. -- So, week 1 is calculated as =COUNTA(D1020), and week 2 is =COUNTA(D2330), week 3 is =COUNTA(D3340) and so on. On my summary page I would like to add each of the weeks up to provide a single number for the month.

I can figure out macros if necessary (but am a beginner at that) and am a novice for VBA scripts too.

Thanks

 

[ariehc]

ack... the smiley face appeared in my post where a colon was. I hope you can get the idea for what I meant..

 

[alow]

You have a couple of options here. You could do this the messy way, and the relatively easy way. The messy way would be to use the following:

=SUM(COUNTA(D10:D20),COUNTA(D23:D30),COUNTA(D33:D40))

And so on, adding each range individually. The next method will work assuming you always have just the letter "y" in the cells you are trying to count, and that just the letter "y" is not present in other cells in the column:

=COUNTIF(D10:D100,"y")

You would, of course, change D100 to the last cell in the column that has the data you are looking for. Let me know if this answers it for you!

 

[ariehc]

okay ... I get the idea... because this workbook may be used by others I am not sure if the counts will be 'y' in every instance. But it is something to consider.

But, in a extension of my original question how do i provide the summary on a different worksheet for the formula =SUM(COUNTA(D1020),COUNTA(D2330),COUNTA(D3340)). (i.e. the worksheet that staff enter data on is titled 'Career Expo' but i want my summary to appear on the 'Outcomes' worksheet.

I really appreciate the help...

 

[ariehc]

I figured it out... thanks for getting me on the right track.

This thread can be closed.

 

[alow]

Great! Just in case anyone else wants to find the answer here, was it the following:

=SUM(COUNTA('Career Expo'!D10:D20),COUNTA('Career Expo'D23:D30),COUNTA('Career Expo'D33:D40))

 

[ariehc]

Great! Just in case anyone else wants to find the answer here, was it the following:

=SUM(COUNTA('Career Expo'!D10:D20),COUNTA('Career Expo'D23:D30),COUNTA('Career Expo'D33:D40))

for some reason separating the items by a comma did not work... I had to use the plus sign. So the formula I ended up using was
=SUM('Career Expo'!D2)+('Career Expo'!D3)+('Career Expo'!D4)+('Career Expo'!D5)+('Career Expo'!D6)

Where ('Career Expo'!D2) equaled =CountA(D10:d20)

Hope this is clear and helps. Again thanks to those who helped me out.

 

[alow]

That's only working for you because you have a close parentheses in there. It should be

=SUM('Career Expo'!D2,'Career Expo'!D3,'Career Expo'!D4,'Career Expo'!D5,'Career Expo'!D6)

And, honestly, if those are the actual cells that you're summing it should just be

=SUM('Career Expo'!D2:D6)

 

[ariehc]

Feeling incredibly Duh after reading the last post. Sometimes there really is a reason to put a project down and do some other task. Doesn't help that the boss keeps changing the desired result. Boss also believes that if it can be imagined then one of us (1 of 4 analysts) should be able to figure out how to capture the data and report it out.