Thanks:  0
Likes:  0

1. I need to count how many times 2 particular requirements were met on a spreadsheet. For example: I have a spreadsheet of cars listed by color and price. I want to know how many red cars listed (column A) are under \$5000.00 (column B). I've tried countif + countif and it just adds the totals instead of distinguishing those that meet both requirements. Any ideas? Thanks.

2. Try using Dcount

=SUMPRODUCT((A1:A100="red")+0,(B1:B100<50000)+0)

Hope this helps,

4. On 2002-04-25 09:59, Autumn wrote:
I need to count how many times 2 particular requirements were met on a spreadsheet. For example: I have a spreadsheet of cars listed by color and price. I want to know how many red cars listed (column A) are under \$5000.00 (column B). I've tried countif + countif and it just adds the totals instead of distinguishing those that meet both requirements. Any ideas? Thanks.
Lets say that you have the color condition in D2 and the price condition in E2. What follows would give you the desired count:

=SUMPRODUCT((A2:A400=D2)*(B2:B400=E2))

where A2:A400 houses the color values and B2:B400 the prices.

5. Thanks for the suggestions. The sumproduct formula worked, my follow up question: is there a way to alter the formula so I can search all of the columns? I mean, say I have 10 columns total, 5 are color and 5 are price, can I "search" all of the columns for my 2 criteria with one formula? By chance?
Thanks again.

User Tag List

Posting Permissions

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