help!

Autumn · Apr 25th, 2002, 10:59 AM

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 · Apr 25th, 2002, 11:09 AM

Try using Dcount

Asala42 · Apr 25th, 2002, 11:10 AM

Hiya, say your talking about through row 100:

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

Hope this helps,
Adam

Aladin Akyurek · Apr 25th, 2002, 11:11 AM

Quote:

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 · Apr 25th, 2002, 11:31 AM

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.

Apr 25th, 2002, 11:09 AM	#2
Ricky Morris Board Regular Join Date: Mar 2002 Posts: 363	Try using Dcount __________________ It's never too late to learn something new. Ricky

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 25th, 2002, 10:59 AM	#1
Autumn New Member Join Date: Apr 2002 Posts: 8	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.

Apr 25th, 2002, 11:10 AM	#3
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,027	Hiya, say your talking about through row 100: =SUMPRODUCT((A1:A100="red")+0,(B1:B100<50000)+0) Hope this helps, Adam

Apr 25th, 2002, 11:31 AM	#5
Autumn New Member Join Date: Apr 2002 Posts: 8	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.