SUMIFS with multiple criteria in different rows

[eldeartho]

Hi everyone,

Apologies I seem to be stuck on what I thought would be a simple formula.

I have a large dataset of customers taking flights (one ways in the dataset) and I am trying to calculate how many are 'most likely' round trip flights (matching by customer name then origin to destination from different rows in the dataset)

The data is laid out below whereby Bob and David would each count as 1 roundtrip but i can't get sumif/s to work.

any advice much appreciated.

[TABLE="width: 485"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Customer[/TD]
[TD]Destination[/TD]
[TD]Origin[/TD]
[TD] Date[/TD]
[TD]Pieces[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]LAX[/TD]
[TD]CVG[/TD]
[TD]19/03/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]CVG[/TD]
[TD]LAX[/TD]
[TD]14/03/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]ATL[/TD]
[TD]LAX[/TD]
[TD]14/03/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]ATL[/TD]
[TD]LAX[/TD]
[TD]14/03/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]ATL[/TD]
[TD]LAX[/TD]
[TD]14/03/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]PBI[/TD]
[TD]LAX[/TD]
[TD]14/03/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]PBI[/TD]
[TD]LAX[/TD]
[TD]14/03/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]PBI[/TD]
[TD]ATL[/TD]
[TD]14/03/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]LAX[/TD]
[TD]JFK[/TD]
[TD]14/03/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]SEA[/TD]
[TD]JFK[/TD]
[TD]05/03/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]JFK[/TD]
[TD]SEA[/TD]
[TD]14/03/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

If "Customer" is in A1.

=SUMPRODUCT(COUNTIFS(A:A,A2:A15,B:B,C2:C15,C:C,B2:B15)) / 2

 

[eldeartho]

Excellent! many thanks.

Is there a way to do the same but calculate the number by the customer name:
Bob = 1
Jack = 0
Mark = 0
David = 1

 

[eldeartho]

nevermind, i know the answer to my own question.. many thanks again.