# Thread: Mutiple Criteria for counting (XP)

1. Dear loungers,

In Excel 2007 you can use COUNTIF with multiple criteria.. However what should I do in XP to acheive the same.

For example I have workbook with a Summary sheet that is based on Change Request data in another sheet. The summary should show Change Requests by Status and Priority - each Change Request have both a status and a priority. This means that in the summary table the left hand column is the Priorities and the top row is the Status value, the body of the sheet contains intersections between these so, for example, I want to count all Change Requests where priority is High and status is Raised. I have attached a simplified version of the sheet but without any formula on the summary sheet. How can I do this?

thank you............................ liz

2. Liz,

Attached is your workbook with an added sheet for criteria ranges.
You need to use the DCount function as shown in the sheet. This will require creating a range for each pair of criteria {Status/Agreed Priority) and naming that range. Note the 2nd parameter in the DCount function is blank but the 2 commas must remain! Also take note of how the values are entered in the criteria ="=raised".

I had a problem getting this to work and I finally figured out that in your Ref Data sheet you had a space behind every one of the status entries you use for your dropdown. I delete these then I had to re-enter all the status entries to make them valid. This is the kind of problem which can be very hard to track down. I knew I had the DCount function right and couldn't figure out why I wasn't getting the correct counts.

Good Luck.

3. Hello - A Pivot Table may do what is needed. See attached sample on Pivot Summary tab

No VBA/Macros are needed.

If detailed trail of any number is needed - just double click the number. For Example: Double click the 13 in medium/raised and you will see the 13 records involved.

Tim

4. I'd use SUMPRODUCT in Excel Prior to 2007.
After that COUNTIFS is faster because it is better optimized for the task

I extended the ranges in your example, the actual range down you use depends on how much data there is likely to be.
Ideally you would use named ranges.

=SUMPRODUCT(('Change Request Examples'!\$E\$3:\$E\$1319=Summary!\$A4)*('Change Request Examples'!\$B\$3:\$B\$1319=Summary!B\$3)*1)

[attachment=89348:Example Sumproduct.xls]

5. Andrew,

Nice
I've never used SumProduct before but thanks to your example I have a grasp on it's use.
I've attached a worksheet using your SumProduct and adding Dynamic Range Names to shorten the formula.
I've also pasted a list of all the defined names and definitions into the Ref Data sheet for reference.

Liz please take notice of the Mixed references used by Andrew and continued in my example which allow you to enter the formula once then copy it down and across.

I just love these forums

6. Andrew,

Just FYI, there is no need for the *1 at the end of yours since you are already coercing the True/False values with multiplication.

7. I know, but you are correct of course, it is redundant in this example.
However, as an example if they only used a single test it would be needed,
so it is a nice safety net to build in.

Also it helps when people look back at it later to indicate that they are using it to count. Well it does for me anyway.

8. You guys are wonderful!!! And I too love the lounge it's the best and most actively contributed to that I know AND by people that really know not by pseudo-experts.

Thank you for all the tips I am now busy making it work - and thank you "retired greek" (I thuoght that was impossible!) for the debugging I too would have been foxed.

long live the lounge!......................... liz

#### Posting Permissions

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