Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Counting with multiple criteria (XL97/WinNT4)

    I think I'm going mad here ... <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I have a spreadsheet which is used to calculate how many people are going to be having lunch in the canteen each day, and of those how many go to each of three sittings. I need to summarise this into a table giving these figures, so I need something along the lines of COUNTIF or SUMIF, but that can use multiple critieria, ie:

    "If item in column D = 1st (they go to the 1st sitting) and item in colum E = y (they're in on Monday) then count 1"

    Obviously, in the summary, there would be five columns for days of the week, three rows for the sittings.

    Surely there's some way of doing this - but I can't track it down! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Any help would be greatly appreciated!
    Beryl M


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting with multiple criteria (XL97/WinNT4)

    A pivot table based on your data would probably do what you want. Post back if you need more help.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Counting with multiple criteria (XL97/WinNT4)

    Thanks Hans, and you're probably right, but I need a plain formula if that's possible ...?
    Beryl M


  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting with multiple criteria (XL97/WinNT4)

    In that case, try the following as an array formula (confirm with Ctrl+Shift+Enter instead of plain Enter):

    =SUM((D137="1st")*(E1:E37 ="y"))

    or

    =COUNT(IF((D137="1st")*(E1:E37="y"),1))

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Counting with multiple criteria (XL97/WinNT4)

    Again, thanks, Hans, but I can't make it work? I'm attaching a copy of the spreadsheet, maybe you can see where I'm going wrong?
    Beryl M


  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting with multiple criteria (XL97/WinNT4)

    In your first post in this thread, you wrote "... If item in column D = 1st ...", so I had assumed that column D contained text items. In your spreadsheet, it contains plain numbers 1, 2 or 3. Therefore, you must not use quotes in the comparison: $D$2:$D$26 = 1 etc. See attached file.

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Counting with multiple criteria (XL97/WinNT4)

    Hans, you're a wiz!

    I wrote it like that in the first place to try to make it clear what I was doing - sorry I confused the issue!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Beryl M


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •