Thread: Array Formula with SumIFS

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!

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

H2:
=PRODUCT(B2,D2)
copied down

then

=SUMIFS($H:$H,$A:$A,">="&DATE(2010,5,1),$A:$A,"<"&DATE(2010,6,1),$G:$G,"ANU",$D:$D,">0")

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

=SUMPRODUCT((TEXT($A$2:$A$100,"mmyyyy")="052010")*($G$2:$G$100="ANU"),$H$2:$H$100)

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 ?

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.