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.

[TABLE="width: 302"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]TYPE[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]NEW HOUSE[/TD]
[TD="align: right"]01/Oct/2017[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]EXISTING HOUSE[/TD]
[TD="align: right"]01/Jan/2017[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]NEW CAR[/TD]
[TD="align: right"]31/Dec/2017[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]EXISTING CAR[/TD]
[TD="align: right"]29/Nov/2017[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Do you also have condition that column A must meet?

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]NAME[/TD]
[TD]TYPE[/TD]
[TD]DATE[/TD]
[TD][/TD]
[TD][/TD]
[TD]HOUSE[/TD]
[TD]CAR[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]A[/TD]
[TD]NEW HOUSE[/TD]
[TD]

01/Oct/2017​

[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]B[/TD]
[TD]EXISTING HOUSE[/TD]
[TD]

01/Jan/2017​

[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]C[/TD]
[TD]NEW CAR[/TD]
[TD]

31/Dec/2017​

[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]A[/TD]
[TD]EXISTING CAR[/TD]
[TD]

29/Nov/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="class: grid, width: 302"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]TYPE[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]NEW HOUSE[/TD]
[TD="align: right"]01/Oct/2017[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]EXISTING HOUSE[/TD]
[TD="align: right"]01/Jan/2017[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]NEW CAR[/TD]
[TD="align: right"]31/Dec/2017[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]EXISTING CAR[/TD]
[TD="align: right"]29/Nov/2017[/TD]
[/TR]
</tbody>[/TABLE]

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)&" "))

[TABLE="class: grid, width: 471"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]NAME[/TD]
[TD]TYPE[/TD]
[TD]DATE[/TD]
[TD][/TD]
[TD]NAME[/TD]
[TD]HOUSE[/TD]
[TD]CAR[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]NEW HOUSE[/TD]
[TD]01/Oct/2017[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]EXISTING HOUSE[/TD]
[TD]01/jan/2017[/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD]NEW CAR[/TD]
[TD]31/Dec/2017[/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A[/TD]
[TD]EXISTING CAR[/TD]
[TD]29/nov/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]******[/TD]
[TD]***************[/TD]
[TD]************[/TD]
[TD]**[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[/TR]
</tbody>[/TABLE]

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