# Thread: Counting with multiple criteria (XL97/WinNT4)

1. ## 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!

2. ## 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. ## Re: Counting with multiple criteria (XL97/WinNT4)

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

4. ## 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. ## 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?

6. ## 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. ## 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>

#### Posting Permissions

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