Counting entries w/ AND condition

**saskin23** · 03-16-2005, 10:30 AM

I have a list of data with 3 columns:

Col A: Dates
Col B: Prices
Col C: Costs

I want to create a table that counts the number of times that the price is more than a certain value and the cost is less than a certain other value in the same month (regardless of the year and day).

For example:

On 2/20/2004 the price was 100 and the cost was 50
On 2/14/2005 the price was 75 and the cost was 75
On 2/18/2005 the price was 50 and the cost was 40

I want to count the number of times that the price is above 60 AND the cost is below 70, given that the month is February. I need a formula for the above data that will return 2. The formula must lookup the month from Col A to do the search.

Any ideas?

**Alex Delamain** · 03-16-2005, 10:43 AM

From your sample data the answer should be 1 as only the data from 20/2/2004 meets both criteria.

The following formula should give you what you need though:

=SUMPRODUCT((MONTH(D2:D25)=2)*(E2:E25>60)*(F2:F25<70))

This will count the number of occasions where month = february (2), Price >60 and cost <70 for data in rows 2-25 (expand as required).
To sum for other months simply change the Month(D2:D25)= to 1-12 for each month.

**saskin23** · 03-16-2005, 10:51 AM

Thanks, Alex. My sample data should indeed return 1, my mistake. Your formula works. My next question is a little more complicated:

I don't really care that there was 1 time in Feb. when both conditions were met--I really just need to know how many times the condidions are met in the same month. So if it was 1 for Feb. and 3 for Mar. and 5 for Apr., I need a formula to return 9. Is there any way to do this? I know I could have 12 of your formula and sum, but is there any way to do this all in 1 formula? I was thinking that some use of Countif() might be appropriate, but I'm not sure if Countif() can match the months in the criteria variable.