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.

Customer	Destination	Origin	Date	Pieces
Bob	LAX	CVG	19/03/2018	1
Bob	CVG	LAX	14/03/2018	1
Jack	ATL	LAX	14/03/2018	1
Jack	ATL	LAX	14/03/2018	1
Jack	ATL	LAX	14/03/2018	1
Jack	PBI	LAX	14/03/2018	1
Jack	PBI	LAX	14/03/2018	1
Mark	PBI	ATL	14/03/2018	1
Mark	LAX	JFK	14/03/2018	1
David	SEA	JFK	05/03/2018	1
David	JFK	SEA	14/03/2018	1

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

 

[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.