VLOOKUP() style but 3 value lookup

Hi all,

I have a table that has, amongst many columns, a year, month and a name column.

I want to do a formula that takes in 3 values (name, year, month) and returns a sum of values found in a variety of other columnswhere these 3 values are matched.

I would know how to do this in SQL but wonder if Excel can do this type of thing on one large data table?

Cheers
David

Well you could use a Pivot Table obviously... this would be the best approach by far.

You could also use Sumproduct ... given vague nature of question concering ranges I will give a generic solution

D1:
=SUMPRODUCT(($E$1:$E$100=$A1)*($F$1:$F$100=$B$1)*($H$1:$H$100=$C$1)*($J$1:$T$100))

Where:

E1:E100 holds Name and A1 Name of interest
F1:F100 holds Year and B1 Year of interest
H1:H100 holds Month and C1 Month of interest
J1:T100 holds values to Sum

I confess that I don't use Tables feature so am not familiar with the syntax... in traditional setup:

=SUMPRODUCT(($E$1:$E$100=$A1)*($F$1:$F$100=$B$1)*($H$1:$H$100=$C$1)*($J$1:$J$100+$M$1:$N$100+$P1:$P100+$T$1:$T$100))

Yes.

Consider:

($E$1:$E$100=$A1) and ($F$1:$F$100=$B$1)

The above will each generate an array of 100 Logical values, eg:

{TRUE,FALSE,...,TRUE} and {FALSE,TRUE,...,TRUE}

When calculating the Product one can view these "Pairs" as

{TRUE * FALSE, FALSE * TRUE, ...., TRUE * TRUE}

By * Logicals one coerces them to their integer equivalents (TRUE=1, FALSE=0 in Native XL)... such that the result of the above pairs becomes:

{0, 0, ..., 1}

Normally I prefer to use

=SUMPRODUCT(--(A1:A100="x"),--(B1:B100="y"),C1:C100)

as this is regarded as being slightly quicker and also little more flexible handling the summation range (C1:C100) HOWEVER this approach only works if the ranges are of equal dimensions which is not the case here (ie tests are 100R x 1C whereas results are say 100R x 4C) thus SUMPRODUCT via * is required.

Does that make sense ?

In truth your life would be a lot easier if you added a further column to your table wherein via formulae you retrieve the appropriate fx rate based on the Date & Currency Code... your life then becomes much easier as you can simply * result by fx column (see red below)

=IFERROR(SUMPRODUCT((DataTbl[partner_manager]=PartnerSelect) * (DataTbl[year]=YearSelect) * (DataTbl[month]=MonthSelect) * (DataTbl[initial] + DataTbl[reinitial] + DataTbl[repeat] + DataTbl[credits] + DataTbl[chargebacks] + DataTbl[refunds] + DataTbl[sales_tax_vat]) * (DataTbl[fx_rate])), "-")

Make sense ?