How do i count in excel? a formula for 2 colomns

    I have a class list in Excel.
    About half of the class are seniors the others juniors. In the fist colomn there's a number(1-27) the second colonm is the rank(senior or junior) and the third colomn is the attendence for a single day(sept. 13) . What formula would i use to calculate how many seniors and how many juniors have attended on a certain date. I need these numbers separately, seniors in one cell and juniors in another.


    Maybe there's a way to do it simpler in Access... any ideas?



    Thank you in advance

    Hi there:
    Welcome to the Board:
    Try this:


    =SUMPRODUCT(($B$2:$B$28=E4)*($C$2:$C$28=F4))


    where B2:B28 is your rank, E4 is your first criteria (i.e. Senior or Junior) and C2:C28 is your date and F4 is your date criteria.



    Hope this will Help.



    Maqbool

    Maqbool's good suggestion can be simplified if all of the entries are for the same date, as seemed to be the case in your example. You could then use the Countif function. With the same cell references as in the Sumproduct example, it would look like
    =COUNTIF($B$2:$B$28,E4)

    Not to beat a dead horse, but if your table is going to grow (multiple dates added as time goes on), you might want to consider using a pivot table. It can give you the junior-senior split and attandence numbers (or percents). And you could use the Page Field for the date to choose which date to view.


    Your data structure will be the determining factor as to whether or not pivot tables will work.

    Thank you very much for the quick reply.
    Worked like a charm, all of them but I was looking more for Maqbool's solution. so double thanks.


    Is there a way to make the same in Access? to be presented in a report?
    -->tt

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login