Sum in Excel using OR Criteria avoiding duplicate SUM

[Deepika]

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

 

[Debaser]

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))))

 

[Monty]

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.

 

[Debaser]

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?

 

[Monty]

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!

 

[Peter Bartholomew]

Using 365

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

 

[bosco_yip]

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)