help!

[Autumn]

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.

 

[Ricky Morris]

Try using Dcount

 

[Asala42]

Hiya, say your talking about through row 100:

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

Hope this helps,
Adam

 

[Aladin Akyurek]

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.

Aladin

 

[Autumn]

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.