Countifs

[xxsalahxx]

I have a spreadsheet with three columns

Col A = Text ( for the purpose of this lets say apples)

Col B = Numbers (I just need to count how many times a number appears)
Example if the colunm has numbers like 23,15,12, this would count as 3

Col C = Dates ( I need to count how many times a date appears) I don't need dates between just how many cells are showing a date

Im trying to count how many times the word apples appears by numbers in column B by number of dates in column C

Thank you in advance for any help you can give me

 

[jproffer]

Something like this maybe?

Excel 2013/2016
	A	B	C	D
2	apples	2	1/3/2018	2
3	bananas	1
4	oranges	1
5	apples	1	1/3/2018
6	oranges	2
7	grapes	2
8	apples	2
9	apples	1	1/3/2018
10	bananas	2
11	oranges	1
12	apples	2	1/3/2018
13	oranges	1
14	grapes	1
15	apples	1	1/3/2018
Sheet1

Cell Formulas
Range	Formula
D2	=COUNTIFS(A2:A15,"apples",B2:B15,2,C2:C15,"1/3/18")

 

[xxsalahxx]

Thank you
The number I would be looking for re your example would be 5
as apples I would count once
If there is a number in b column that's a match and there is dates also matching but im getting a zero. any ideas whats wrong?
Thanks you your help is appreciated

Something like this maybe?

Excel 2013/2016

	A	B	C	D

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: center"]apples[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1/3/2018[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]bananas[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]oranges[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]apples[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1/3/2018[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]oranges[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]grapes[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]apples[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]apples[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1/3/2018[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]bananas[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]oranges[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]apples[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1/3/2018[/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]oranges[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]grapes[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]apples[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1/3/2018[/TD]
[TD="align: center"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=COUNTIFS(A2:A15,"apples",B2:B15,2,C2:C15,"1/3/18")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jproffer]

Could be the date format. When I first tried it, I got zero as well. After I added the quotes around the date in the formula and formatted the date cells to: DATE "1/3/18", then it worked.

I was just hoping that was something you could live with.

 

[xxsalahxx]

Does it have to be countif or maybe sumproduct? Im still getting a zero result.

Could be the date format. When I first tried it, I got zero as well. After I added the quotes around the date in the formula and formatted the date cells to: DATE "1/3/18", then it worked.

I was just hoping that was something you could live with.

 

[jproffer]

If you're willing to put the specified date into another cell (or maybe it already is), this seems to eliminate the formatting problem:

Excel 2013/2016
	A	B	C	D	E
1				MyDate
2	apples	2	1/3/2018	1/3/2018	2
3	bananas	1
4	oranges	1
5	apples	1	1/3/2018
6	oranges	2
7	grapes	2
8	apples	2
9	apples	1	1/3/2018
10	bananas	2
11	oranges	1
12	apples	2	1/3/2018
13	oranges	1
14	grapes	1
15	apples	1	1/3/2018
Sheet1

Cell Formulas
Range	Formula
E2	=COUNTIFS(A2:A15,"apples",B2:B15,2,C2:C15,D2)