Mapping? -wondering if this is workable in Sumproduct/index/match?

[Long Nose]

Hi All,

I have a question that I can't figure without adding additional columns to the data to match missing descriptions. I have a short list with cost and descriptions to apply to a larger list with codes that relate to the descriptions, yet the descriptions are not present in the larger list with the data codes. I want to sum the transaction days below. I realize I could add a column to the data, I just wanted to know if I can do it without adding any columns inserting the formula where "?".

Additionally, if I only want ZE and ALL from the data.

Apologies in advance for not knowing how to post a picture

TABLE 1

FLEET_BASE_SET......AVG........Transaction Days
ADRENALINE.............18..............?...[ANSWER HERE IS 20]
COMPACT..................9...............?...[ANSWER HERE IS 940]
CONVERTIBLE...........23..............?
DREAM.....................33..............?
MIDSIZE...................10..............?

TABLE 2

Mapping
CAR_CLASS.......CAR_GROUP
A......................COMPACT
A4.....................DREAM
A6.....................COMPACT
B.......................COMPACT
B4.....................ADRENALINE
B6.....................COMPACT
C.......................MIDSIZE
C4.....................MIDSIZE
C6.....................DREAM
D.......................MIDSIZE

DATA

DATA
BUCKET......RNT_BRD_CD......CAR_CLASS_CHARGED......DESCR...........REGION
ALL...............ZE..............................A.........................Days.................1,549
ALL...............ZE..............................A....................TRANSACTIONS.........326
ALL...............ZE..............................B..........................Days................2,145
ALL...............ZE..............................B....................TRANSACTIONS.........430
ALL...............ZE..............................C..........................Days..................856
ALL...............ZE..............................C.....................TRANSACTIONS.......130
ALL ZE D Days 268
ALL ZE D TRANSACTIONS 56
ALL ZE A4 Days 1,377
ALL ZE A4 TRANSACTIONS 222
ALL ZE A6 Days 255
ALL ZE A6 TRANSACTIONS 47
ALL ZE B4 Days 107
ALL ZE B4 TRANSACTIONS 20
ALL ZE B6 Days 868
ALL ZE B6 TRANSACTIONS 137

 

[Marcelo Branco]

Maybe something like this

Table 2 --> gray area
DATA --> light blue area

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

FLEET_BASE_SET​

AVG​

Transaction Days​

CAR_CLASS​

CAR_GROUP​

BUCKET​

RNT_BRD_CD​

CAR_CLASS_CHARGED​

DESCR​

REGION​

2​

ADRENALINE​

18​

20​

A​

COMPACT​

ALL​

ZE​

A​

Days​

1549​

3​

COMPACT​

9​

940​

A4​

DREAM​

ALL​

ZE​

A​

TRANSACTIONS​

326​

4​

CONVERTIBLE​

23​

0​

A6​

COMPACT​

ALL​

ZE​

B​

Days​

2145​

5​

DREAM​

33​

222​

B​

COMPACT​

ALL​

ZE​

B​

TRANSACTIONS​

430​

6​

MIDSIZE​

10​

186​

B4​

ADRENALINE​

ALL​

ZE​

C​

Days​

856​

7​

B6​

COMPACT​

ALL​

ZE​

C​

TRANSACTIONS​

130​

8​

C​

MIDSIZE​

ALL​

ZE​

D​

Days​

268​

9​

C4​

MIDSIZE​

ALL​

ZE​

D​

TRANSACTIONS​

56​

10​

C6​

DREAM​

ALL​

ZE​

A4​

Days​

1377​

11​

D​

MIDSIZE​

ALL​

ZE​

A4​

TRANSACTIONS​

222​

12​

ALL​

ZE​

A6​

Days​

255​

13​

ALL​

ZE​

A6​

TRANSACTIONS​

47​

14​

ALL​

ZE​

B4​

Days​

107​

15​

ALL​

ZE​

B4​

TRANSACTIONS​

20​

16​

ALL​

ZE​

B6​

Days​

868​

17​

ALL​

ZE​

B6​

TRANSACTIONS​

137​

18​

Array formula in C2 copied down
=SUM(SUMIFS(L$2:L$17,K$2:K$17,"TRANSACTIONS",J$2:J$17,IF($F$2:$F$11=A2,E$2:E$11)))
Ctrl+Shift+Enter

Hope this helps

M.

 

[Long Nose]

Thank you Marcelo.

It works!

-Doug