SUMPRODUCT function with heaps of IFS

Hi Guys,

I have encountered the following problem which I'm breaking my head over.

I have this formula:

Code:

---

=SUMPRODUCT(--(ISNA(MATCH(PaxData[Sector],Parameters!$C$5:$C$61,0))),--(PaxData[Driver]=Analysis!B6),--(PaxData[Date]>=Analysis!$C$1),--(PaxData[Date]<=Analysis!$C$2),PaxData[Pax])

---

Where PaxData is a table consisting of following columns: Driver - Date - Thread - Sector - Pax
Where Parameter!C5:C61 is a list of sectors to exclude,
Analysis!B6 is the driver to return results for,
AnalysisC1 and C2 are the start and end date of the desired period.

Now this is working fine, it return me the sum of passengers:
- Not in the ecluded sectors
- For the driver in B6
- Between the desired dates

But, I would like to change something in this. Some records have the same date, whihc gives me a skewed result, so what I would like to achieve is to have above criteria, but when that returns 2 or more numbers from the same date, that it takes the average of those. Now I have tried to modify the formula, but I obviously having idea what I'm doing, I've come up with following.

Code:

---

=SUMPRODUCT(--(ISNA(MATCH(PaxData[Sector],Parameters!$C$5:$C$61,0))),--(PaxData[Driver]=Analysis!B5),--(PaxData[Date]>=Analysis!$C$1),--(PaxData[Date]<=Analysis!$C$2),--(IF(PaxData[Date]=PaxData[Date],AVERAGE(PaxData[Pax]),PaxData[Pax])))

---

Does anyone knows if it is possible what i'm trying to achieve? and if so, how?

Any help will be much appreciated!


Thanks heaps! :)

Again untested.

=SUMPRODUCT(--(ISNA(MATCH(PaxData[Sector],Parameters!$C$5:$C$61,0))),--(PaxData[Driver]=Analysis!B6),--(PaxData[Date]>=Analysis!$C$1),--(PaxData[Date]<=Analysis!$C$2),PaxData[Pax])/SUMPRODUCT(--(ISNA(MATCH(PaxData[Sector],Parameters!$C$5:$C$61,0))),--(PaxData[Driver]=Analysis!B6),--(PaxData[Date]>=Analysis!$C$1),--(PaxData[Date]<=Analysis!$C$2))

Hi Admin,

Thanks for you help again,

I'm pretty sure this returns the average per day, doesn't it? I can work with this, but was actually looking for a formula that:

- Looks for all the criteria
- Then checks if there are double dates (there will be a max of 2, based on the other criteria)
- Take the averages of those double records (max 2)
- Then sums up all of it

ie, Following records:

Date - Driver - Sector - Thread - Pax

1/1/2015 - John - 1 - A - 12
1/1/2015 - John - 2 - A - 13
16/1/2015 - John - 1 - B - 12
16/1/2015 - John - 2 - B - 14
2/2/2015 - John - 1 - C - 16
2/3/2015 - John - 1 - C - 17
2/4/2015 - John - 1 - C - 24

So for criteria: Driver John, between 1/01/2015 and 15/02/2015, Minus excluded threads(D or whatever) will return the sum of following:

1/1/2015 - John - 1 - A - 12
1/1/2015 - John - 2 - A - 13
16/1/2015 - John - 1 - B - 12
16/1/2015 - John - 2 - B - 14
2/2/2015 - John - 1 - C - 16

And I want the formula to average the doubles, per date, before summing them. So average these:

1/1/2015 - John - 1 - A - 12
1/1/2015 - John - 2 - A - 13

and these

16/1/2015 - John - 1 - B - 12
16/1/2015 - John - 2 - B - 14

and then sum those averages with

2/2/2015 - John - 1 - C - 16


Hope this is clear and hope it's possibel :D

Thanks,

Hi

I think you better to create a pivot table for this. Place the Date and Driver in the Page field and Thread in Row field and obviously Pax in Values.