sumif count trips

makinmomb

Active Member
Joined
Dec 23, 2013
Messages
401
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

DATEGOMKASKITLUBLUSNDO
1-Sep-1815
2-Sep-18251

<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
 

Some videos you may like

Excel Facts

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

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">DATE</td><td style=";">GOMA</td><td style=";">KASUMBALESA</td><td style=";">KIT</td><td style=";">LUBUMBASHI</td><td style=";">LUSAKA</td><td style=";">NDO</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1-Sep-18</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">2-Sep-18</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">W2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$B$2:$B$15,$V2,$F$2:$F$15,W$1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">X2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$B$2:$B$15,$V2,$F$2:$F$15,X$1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Y2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$B$2:$B$15,$V2,$F$2:$F$15,Y$1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Z2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$B$2:$B$15,$V2,$F$2:$F$15,Z$1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AA2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$B$2:$B$15,$V2,$F$2:$F$15,AA$1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AB2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$B$2:$B$15,$V2,$F$2:$F$15,AB$1</font>)</td></tr></tbody></table></td></tr></table><br /> copy down
 

makinmomb

Active Member
Joined
Dec 23, 2013
Messages
401
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,422
Messages
5,547,834
Members
410,813
Latest member
Vhinzvirgo
Top