Nested Countif

[Degaulle]

Hi

Is it possible to do a nested countif?

I want to count the occurances of a value based on a date specifed?

 

[Yogi Anand]

Hi Degaulle:

It can be done using COUNTIF or one of the other functions but please show us a few rows of your sample data and the expected result.

 

[barry houdini]

How do you mean, exactly?

Do you want to count values in one column based on a date in another or do you just want to count dates which fall within a certain time frame?

 

[Degaulle]

In Column A i have different type of fruit. In column B i have delivery dates.

I want to count how many times Apples appears in coumn A on a specific date in B?

 

[barry houdini]

=sumproduct(--(a1:a10="apples"),--(b1:b10=D1))

where D1 contains your date

 

[Degaulle]

That does not seem to work? i get a #NUM!

Doesnt sumproduct mulitply the values?

 

[barry houdini]

With sumproduct you can't use whole column references like A:A you need to use a1:a100 or something similar, all ranges to be the same size.

Yes, it does multiply values, but in this case the values in question will be 1s and 0s only, effectively giving you a count - see this example

Book1
	A	B	C	D	E
1	apples	17-Nov-05		17-Nov-05
2	bananas	18-Nov-05
3	apples	19-Nov-05		2
4	pears	17-Nov-05
5	grapefruit	17-Nov-05
6	kiwi	17-Nov-05
7	apples	20-Nov-05
8	bananas	17-Nov-05
9	apples	17-Nov-05
10	lemos	17-Nov-05
11
Sheet2

 

[Yogi Anand]

Hi Degaulle:

If you want to use the whole column, you may want to use the the DCOUNT or DCOUNTA function ...

Book1
	A	B	C	D	E
1	Fruit	Date		Date	Fruit
2	apple	15-Nov-05		11/15/2005	apple
3	banana	16-Nov-05
4	pear	15-Nov-05		2
5	apple	16-Nov-05
6	banas	15-Nov-05
7	apple	15-Nov-05
8	pear	15-Nov-05
9
Sheet1

 

[dwerden]

Hi Degaulle,
the formula does work as shown in the sheet below. You may want to check how you entered it. I use something similar to get data and the sumproduct formula is a great tool for what you need it to do.

Don

Book1
	A	B	C	D	E
1	Fruit	Del Date		1-Nov	3
2	Apples	1-Nov		2-Nov	2
3	Apples	1-Nov		3-Nov	0
4	Pears	3-Nov
5	Kiwi	3-Nov
6	Grapes	4-Nov
7	Bananas	4-Nov
8	Apples	1-Nov
9	Cherries	3-Nov
10	Apples	2-Nov
11	Apples	2-Nov
Sheet1

 

[Degaulle]

Thanks, they all work now!