Count no of items between dates when dates are in a column

[St Vitus Dance]

Hello Everyone,

I am trying to count the no of items between two dates (say from 01/Oct/2017 to 31/Dec/2017) using criteria in column a. Each item has a date in a row in the format dd/mmm/yyyy. I'd rather not have to enter the dates in separate cells and rather have them in the formula so that they use the dates in column c. Also, id like to be able to use a wild card within the formula to be able to count anything with 'HOUSE' and anything with 'CAR'

My data would look like this... appreciate any help folks, thanks for your time.

NAME	TYPE	DATE
A	NEW HOUSE	01/Oct/2017
B	EXISTING HOUSE	01/Jan/2017
C	NEW CAR	31/Dec/2017
A	EXISTING CAR	29/Nov/2017

<tbody>
</tbody>

 

[Aladin Akyurek]

Do you also have condition that column A must meet?

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​
1​	NAME	TYPE	DATE			HOUSE	CAR
2​	A	NEW HOUSE	01/Oct/2017​		A	1​	1​
3​	B	EXISTING HOUSE	01/Jan/2017​		B	0​	0​
4​	C	NEW CAR	31/Dec/2017​		C	0​	1​
5​	A	EXISTING CAR	29/Nov/2017​

<tbody>
</tbody>

F2=COUNTIFS($A$2:$A$5,$E2,$B$2:$B$5,"*"&F$1&"*",$C$2:$C$5,">="&$C$2,$C$2:$C$5,"<="&$C$4) copy across and down

 

[St Vitus Dance]

Do you also have condition that column A must meet?

Yes please, anything with 'A' in it

thanks

 

[Aladin Akyurek]

=SUM(COUNTIFS(A:A,"A",B:B,"*"&{"HOUSE","CAR"}&"*",C:C,">="&DATE(2017,10,1),C:C,"<="&DATE(2017,12,31)))

This counts records with either HOUSE or CAR together.

If you want to count these partial items separately...

=COUNTIFS(A:A,"A",B:B,"*"&"HOUSE"&"*",C:C,">="&DATE(2017,10,1),C:C,"<="&DATE(2017,12,31))

=COUNTIFS(A:A,"A",B:B,"*"&"CAR"&"*",C:C,">="&DATE(2017,10,1),C:C,"<="&DATE(2017,12,31))​

 

[markmzz]

Hello Everyone,

I am trying to count the no of items between two dates (say from 01/Oct/2017 to 31/Dec/2017) using criteria in column a. Each item has a date in a row in the format dd/mmm/yyyy. I'd rather not have to enter the dates in separate cells and rather have them in the formula so that they use the dates in column c. Also, id like to be able to use a wild card within the formula to be able to count anything with 'HOUSE' and anything with 'CAR'

My data would look like this... appreciate any help folks, thanks for your time.

NAME	TYPE	DATE
A	NEW HOUSE	01/Oct/2017
B	EXISTING HOUSE	01/Jan/2017
C	NEW CAR	31/Dec/2017
A	EXISTING CAR	29/Nov/2017

<tbody>
</tbody>

Maybe this:

Normal Formula (use Enter to enter the formula)

In E2 and copy down: =IFERROR(INDEX($A$2:$A$5,MATCH(0,INDEX(COUNTIF($E$1:$E1,$A$2:$A$5),0),)),"")

Array Formula (use Ctrl+Shift+Enter to enter the formula)

In F2 and copy down and to right: =COUNT(SEARCH(" "&F$1&" "," "&IF($A$2:$A$5=$E2,$B$2:$B$5)&" "))

	A	B	C	D	E	F	G
1	NAME	TYPE	DATE		NAME	HOUSE	CAR
2	A	NEW HOUSE	01/Oct/2017		A	1	1
3	B	EXISTING HOUSE	01/jan/2017		B	1	0
4	C	NEW CAR	31/Dec/2017		C	0	1
5	A	EXISTING CAR	29/nov/2017
***	******	***************	************	**	*******	*******	*******

<tbody>
</tbody>

Markmzz

 

[markmzz]

You can try this too (Array Formula - use Ctrl+Shift+Enter to enter the formula):

=COUNT(SEARCH(" "&F$1&" "," "&IF($A$2:$A$5=$E2,IF($C$2:$C$5>=--"1/10/2017",IF($C$2:$C$5<=--"31/12/2017",$B$2:$B$5)))&" "))

Markmzz

 

[markmzz]

One small modification (Array Formula - use Ctrl+Shift+Enter to enter the formula):

=COUNT(SEARCH(" "&F$1&" ",IF($A$2:$A$5=$E2,IF($C$2:$C$5>=--"1/10/2017",IF($C$2:$C$5<=--"31/12/2017"," "&$B$2:$B$5&" ")))))

Markmzz