Array Formula with SumIFS

**Avinash Beepath** · 02-02-2011, 01:05 PM

Good day folks,

Can anyone please assist me in constructing a formula to calculate the following as I've tried different techniques but still haven't been able to get exactly what I'm looking for!

On the attached sheet I need one formula for a dashboard that will filter for example

May-10
Adults >0
Brd = ANU

then here is the tricky part i need the net value per passenger multiplied by the amt of adults after those filters above have been applied. The answer here would be $1,849.

Hopefully i described this problem properly and thank you in advance!

**DonkeyOte** · 02-02-2011, 01:09 PM

If you store the Product adjacent to your data you can just use SUMIFS, eg:

Please Login or Register  to view this content.

then

Please Login or Register  to view this content.

If not you need to use SUMPRODUCT and at that point you need to restrict the ranges you specify as it's inefficient:

Please Login or Register  to view this content.

**Avinash Beepath** · 02-02-2011, 01:34 PM

Great! Thank you very much..
If i am using the second option, can i use "counta" to deduce the last cell in the range? the entire sheet has about 90K lines...would this be very inefficient?

**DonkeyOte** · 02-02-2011, 01:36 PM

For a one off calculation the 90k would be slow but probably not catastrophic.

Are you adopting SUMPRODUCT for sake of backwards compatibility or other ?

**Avinash Beepath** · 02-02-2011, 01:41 PM

I am using it for an annual file but finishing the 2010 workbook...there shouldnt be more than perhaps 130K lines but based on your point I'm wondering if performance would be severely hampered.

by the way...it works great, just as I requested. Thank you for your input!

**DonkeyOte** · 02-02-2011, 01:45 PM

I'd strongly advise use of Product column and SUMIFS if working with large precedent ranges.

If you need to return multiple combinations you might consider using a Pivot Table.