EXCEL Array SUM IF unique combinations of multiple criteria

Question

Is it possible to create an array formula based on a table where it searches for distinct combinations of data before summing? Here is some sample data:

   |    A     |     B     |     C     |     D     |
---------------------------------------------------
 1 | 1 Jun    | Charlie   | D         | 1.3       |
 2 | 1 Jun    | Charlie   | N         | 1.4       |
 3 | 1 Jun    | Dave      | D         | 1.3       |
 4 | 2 Jun    | Charlie   | N         | 0.6       | 
 5 | 2 Jun    | Dave      | D         | 1.5       |

What I'd like the array formula to be able to do is look at the table, ignore column B and tell me which distinct rows (A, C and D or more criteria) and sum column D if they are unique. In this example, it should not sum Row 3 because on 1 Jun, "D" already has a 1.3.

PivotTables and VBA are out as I am designing this for the lowest level user on super restrictive computers.

EDIT: Picture of expected results, changed sample data to reflect results.

Kind of the data I am looking for.... I replaced the ID with the LName to visualize. I need to exclude individuals and get only aircraft hours by their modes. You can see toward the bottom of the image, 2 people are on one aircraft. I used MAX to keep it from SUMming it up and skewing the hours.

The levels on the left are by Date, Aircraft, Flight Number, then Person. I need to aggregate the hours down to the Flight Number of each date.

Pivot Table

The original formula is

=SUM((FLTD_HRS)*(FLTD_DATE>=$D$3)*(FLTD_DATE<=$D$4)*(IF(FLTD_ACFT_MDS=CONFIG!$F$8,1,0)))

however, as it is, does not get me unique flights. I'd prefer an array formula to a PivotTable.... XD

"Sum if they are unique" doesn't make any sense to me. You want it to ignore a particular row because... why now? Your expected results don't line up with your sample data. I'm just not sure what you are trying to do. — JNevill
– JNevill, Commented Nov 29, 2017 at 21:38
There is more than just 4 columns of data in my raw data table. This data is exported by a database from the web with about 5000 rows or more. The output file creates rows for each person in each mode of travel by vehicle, then by date. Which means that 2 or more different people can have the same travel date with the same vehicle for the same amount of hours. I need to extract the hours for each unique day/vehicle/mode so that it only counts it once regardless of how many people were in it. Think of the hours an airplane flies regardless of passengers. — Karl
– Karl, Commented Nov 29, 2017 at 21:43
I don't quite follow why a PivotTable would be out just because your users are low level...they don't have to actually do anything with the PivotTable but look at the results it serves up, surely? — jeffreyweir
– jeffreyweir, Commented Nov 29, 2017 at 21:53

Spencer · Accepted Answer · 2017-12-01 15:38:05Z

0

Create a table with the unique combos of A, C, and D. This works assuming finite or at least non-changing but you can also just add every combination you could possibly have and add an IF(row="","",x) to the formula to not show.

You can add a new column (E) to your first table and then lookup the value in the new table or calculate in the new table.

 E = SUMIFS(D$3:D$30,A$3:A$30,A3,C$3:C$30,C3,D$3:D$30,D3)
E1 = {=INDEX(A$3:E$30,MATCH(G3&H3&I3,A$3:A$30&C$3:C$30&D$3:D$30,0),5)}
E2 = SUMIFS(D$3:D$30,A$3:A$30,G3,C$3:C$30,H3,D$3:D$30,I3)

answered Dec 1, 2017 at 15:38

Spencer

1079 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Karl · Accepted Answer · 2017-12-01 20:21:28Z

0

I ended up creating a calculation sheet to create a unique key for each flight by concatenating the fields I wanted to match and also eliminated the duplicates by searching for "VALID_PC" which searched for the highest rank of the person in the crew flying. The formulas I used in columns K and L were:

=IF(IFERROR(MATCH(H3,VALID_PC,0),"0"),A3&RIGHT(C3,5)&D3&E3,0)

and

=IFERROR(IF(A2=0,0,IF(COUNTIF($K$2:K3,K3)>1,0,1)),0)

This allowed me to do a simple {=SUM} on whether column L had a 1 in it on the main page..

Calculations Sheet

I do appreciate the help and effort you all put in to help me.

answered Dec 1, 2017 at 20:21

Karl

256 bronze badges

Collectives™ on Stack Overflow

EXCEL Array SUM IF unique combinations of multiple criteria

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related