# Nested Countif

#### Degaulle

##### Board Regular
Hi

Is it possible to do a nested countif?

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

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.

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?

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?

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

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

Doesnt sumproduct mulitply the values?

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
ABCDE
1apples17-Nov-0517-Nov-05
2bananas18-Nov-05
3apples19-Nov-052
4pears17-Nov-05
5grapefruit17-Nov-05
6kiwi17-Nov-05
7apples20-Nov-05
8bananas17-Nov-05
9apples17-Nov-05
10lemos17-Nov-05
11
Sheet2

Hi Degaulle:

If you want to use the whole column, you may want to use the the DCOUNT or DCOUNTA function ...
Book1
ABCDE
1FruitDateDateFruit
2apple15-Nov-0511/15/2005apple
3banana16-Nov-05
4pear15-Nov-052
5apple16-Nov-05
6banas15-Nov-05
7apple15-Nov-05
8pear15-Nov-05
9
Sheet1

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
ABCDE
1FruitDel Date1-Nov3
2Apples1-Nov2-Nov2
3Apples1-Nov3-Nov0
4Pears3-Nov
5Kiwi3-Nov
6Grapes4-Nov
7Bananas4-Nov
8Apples1-Nov
9Cherries3-Nov
10Apples2-Nov
11Apples2-Nov
Sheet1

Thanks, they all work now!

Replies
3
Views
113
Replies
6
Views
178
Replies
7
Views
172
Replies
2
Views
122
Replies
4
Views
137

1,203,552
Messages
6,056,053
Members
444,841
Latest member
SF_Marnie

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back