# Thread: Sum using three criteria (2002)

1. ## Sum using three criteria (2002)

Calculating total number of occurrences using three criteria. The attached spread sheet explains all.
I need to be able to get a total number of occurrences based on company name, incident type within a certain month.
Any help would be greatly appreciated.

<img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

2. ## Re: Sum using three criteria (2002)

Hi Dean,

Try the attached. I've used array formulae to do this, setting up one in the top-right corner for each make & copying down & accross. Note that named ranges for your table would make this easier than relying on cell references.

Cheers

3. ## Re: Sum using three criteria (2002)

Thanks for the help. I tried to use the array formulae (with changed references) in the speadsheet attached and for some reason it does not seem to calculate. Also thinking about it further also need to count (ie Jan 03 v Jan 04) by year. You can see the data in Working Incident 04 (tab) and the required numbers in Stats Details (tab). Would you mind suggesting what I need to do - very much appreciated. <!help=01>Help 01<!/help> <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

4. ## Re: Sum using three criteria (2002)

Perhaps you want something slightly different from macropod's solution. See attached zipped workbook; it takes month and year into account. I also included a pivot table, just as an example.

5. ## Re: Sum using three criteria (2002)

<P ID="edit" class=small>(Edited by macropod on 12-Dec-03 21:32. Updated to incorporate year into formula)</P>Hi Dean,

See attached. Your workbook didn't have any formulae so that I could see where the problem was, but there were some fairly basic problems, including your defined names being in different columns than the equivalent data, and using LTIs instead of just LTI on the Stats sheet.

Cheers

#### Posting Permissions

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