Sum in Excel using OR Criteria avoiding duplicate SUM

Deepika

New Member
Hi,

I am seeking support to help me with an excel formula which provides sum of PAX Count wherein any of the below conditions are met, but should not double count
OD = ICNMAN
Class = Economy
Tour Code = A12345

The result I am expecting is - 92.
Rows highlighted in Red should not be double counted.

Any quick help is appreciated!

 OD Class TourCode PAX Count LHRDEL Economy A12345 20 ICNMAN Economy 32 AMDYYZ Business 14 ICNMAN Business A12345 18 LHRDEL Business 16 AUHDEL Economy 22

Assuming that is in A1:D7:

=SUM(IF(A2:A7="ICNMAN",D2:D7,IF(B2:B7="Economy",D2:D7,IF(C2:C7="A12345",D2:D7,0))))

In Excel, you can use the SUMIFS function to sum values based on OR criteria while avoiding duplicate sums.

The SUMIFS function allows you to sum values that meet multiple criteria. By using the OR logic in the criteria, you can sum values that meet at least one of the given conditions. A possible formula could look like this:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

In this formula:
- sum_range is the range of cells to sum
- criteria_range1 is the first range to apply the OR criteria
- criteria1 is the first criteria to match in criteria_range1
- criteria_range2 is the second range to apply the OR criteria
- criteria2 is the second criteria to match in criteria_range2

By using the SUMIFS function in this way, you can avoid duplicate sums and restrict the sum based on the specified conditions using OR logic.

Monty,
Given that you can't simply use OR in the criteria (unless you actually happen to be trying to match True/False values), I'm not sure how this helps?

Hello Debaser

You're correct; my apologies for any confusion. While the SUMIFS function doesn't support direct OR logic in its criteria, you can emulate it by summing the results of multiple SUMIFS functions. For instance:

=SUMIFS(sum_range, criteria_range1, criteria1) + SUMIFS(sum_range, criteria_range2, criteria2)

This formula sums the values based on the first set of criteria and adds the sum of values based on the second set of criteria. It effectively combines the OR logic for the desired outcome.

I hope this clarifies the approach. Let me know if you have further questions or if there's anything else I can assist you with!

Using 365

Code:
``````= LET(
matches?, description=TOROW(selection),
include?,  BYROW(matches?, ORλ),
SUM(FILTER(PAX_Count, include?))
)``````

Assuming that is in A1:D7: =SUM(IF(A2:A7="ICNMAN",D2:D7,IF(B2:B7="Economy",D2:D7,IF(C2:C7="A12345",D2:D7,0))))

Or, using SUMPRODUCT function instead of SUM+IF

=SUMPRODUCT((((A2:A7="ICNMAN")+(B2:B7="Economy")+(C2:C7="A12345"))>0)*D2:D7)