# SUMIFS with multiple criteria in different rows

#### eldeartho

##### New Member
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.

 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>

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### mikerickson

##### MrExcel MVP
If "Customer" is in A1.

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

#### eldeartho

##### New Member
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

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