# sumif count trips

#### makinmomb

##### Active Member
I have this on column B

 DATE 1-Sep-18 1-Sep-18 1-Sep-18 1-Sep-18 1-Sep-18 1-Sep-18 2-Sep-18 2-Sep-18 2-Sep-18 2-Sep-18 2-Sep-18 2-Sep-18 2-Sep-18 2-Sep-18

<colgroup><col></colgroup><tbody>
</tbody>

I have this on column F
DESTINATION
KASUMBALESA
LUBUMBASHI
LUBUMBASHI
LUBUMBASHI
LUBUMBASHI
LUBUMBASHI
LUBUMBASHI
LUBUMBASHI
GOMA
LUBUMBASHI
LUSAKA
LUBUMBASHI
GOMA
KOLOWEZI

I have this on v2 onwards

 DATE GOM KAS KIT LUB LUS NDO 1-Sep-18 1 5 2-Sep-18 2 5 1

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>

I want formula on w2 rightwards drag down to do the
count automatically , I have shortened the name of the
locations , but assume the names are exactly in long length
so that the sumif catches the count of trips

Moto of the formula count trip to destination date wise without
computing them manually

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### makinmomb

##### Active Member
works with this on w2 drag right but does not work on copy down

=COUNTIFS(\$B\$2:\$B\$1000,\$V3,\$F\$2:\$F\$1000,W\$2)

#### makinmomb

##### Active Member
I want the drag down to work

##### MrExcel MVP
I want the drag down to work

In W2 enter, copy across, and down:

=COUNTIFS(\$A\$2:\$A\$15,\$V2,\$B\$2:\$B\$15,W\$1&"*")

very simple

Replies
3
Views
200
Replies
9
Views
316
Replies
1
Views
225
Replies
4
Views
219
Replies
1
Views
152