INDEX, MATCH OR XLOOKUP?

[mickeystanford_alumni]

Hi, hope you are all well.
I am trying to build a formula with a sum of sumifs which one of the criterias references an index I am creating. The data looks like this:

=SUM(SUMIFS(C:C,A,A,"2010",D:D, here I want the formula to read the index "cars" in this case and kind of transpose those so that the sum includes in this case all Car brands. The result in this case should be 66.

The data is quite big so that is why I need it, to keep data clean and if any new brand gets added, I can easily include it in the data without the need to change the formula.

Thank you very much.

DATA

YEAR	MONTH	SALES	BRAND
2010​	1​	1​	MERCEDES
2010​	2​	15​	MERCEDES
2010​	3​	20​	MERCEDES
2010​	4​	30​	PORSCHE

INDEX

	BRAND
CARS	MERCEDES	PORSCHE
PLANES	BOEING	JET

 

[RobP]

Hi,

its not quite so easy with the version of Excel you have, so in this case I would offer you this kind of solution, where you would give yourself a "helper" column as such.

I'm not sure if thats workable for you or not, but essentially the formula in E is establishing upfront if the car is in your Index List (eg. if E > 0). The formula can then work on that quite simply.

Book1
	A	B	C	D	E	F	G	H	I
1	YEAR	MONTH	SALES	BRAND	Helper
2	2010	1	1	MERCEDES	1				SUMIFS
3	2010	2	15	FORD	0				51
4	2010	3	20	MERCEDES	1
5	2010	4	30	PORSCHE	2
6
7
8	INDEX
9		BRAND
10	CARS	MERCEDES	PORSCHE
11	PLANES	BOEING	JET
Sheet1

Cell Formulas
Range	Formula
I3	I3	=SUMIFS(C2:C5,A2:A5,"=2010",E2:E5,">0")
E2:E5	E2	=IFNA(MATCH(D2,B$10:B$11),0)

 

[Kerryx]

Ever think Pivot Table option ?

 

[mickeystanford_alumni]

Hi,

its not quite so easy with the version of Excel you have, so in this case I would offer you this kind of solution, where you would give yourself a "helper" column as such.

I'm not sure if thats workable for you or not, but essentially the formula in E is establishing upfront if the car is in your Index List (eg. if E > 0). The formula can then work on that quite simply.

Book1
	A	B	C	D	E	F	G	H	I
1	YEAR	MONTH	SALES	BRAND	Helper
2	2010	1	1	MERCEDES	1				SUMIFS
3	2010	2	15	FORD	0				51
4	2010	3	20	MERCEDES	1
5	2010	4	30	PORSCHE	2
6
7
8	INDEX
9		BRAND
10	CARS	MERCEDES	PORSCHE
11	PLANES	BOEING	JET
Sheet1

Cell Formulas
Range	Formula
I3	I3	=SUMIFS(C2:C5,A2:A5,"=2010",E2:E5,">0")
E2:E5	E2	=IFNA(MATCH(D2,B$10:B$11),0)

Thank you Rob. Is it easier with any other version of excel? I can upgrade/update it if that would help.
Your way works, though I would prefer not to include the helper column.

Let me know if you could find a different way, as I am a bit stuck.

 

[mickeystanford_alumni]

Thank you Rob. Is it easier with any other version of excel? I can upgrade/update it if that would help.
Your way works, though I would prefer not to include the helper column.

Let me know if you could find a different way, as I am a bit stuck.

Anything?
Txs in advance

 

[Fluff]

How about

=SUM(FILTER(C2:C5,(A2:A5=2010)*(ISNUMBER(XMATCH(D2:D5,B10:C10)))))

 

[mickeystanford_alumni]

How about

=SUM(FILTER(C2:C5,(A2:A5=2010)*(ISNUMBER(XMATCH(D2:D5,B10:C10)))))

Hi Fluff, that works indeed, txs.

However, the objective is to link the second part of your formula with "CARS" & "PLANES" etc...scrolling down, not just selecting the Cars brands in the XMATCH.

Then the formula, by reading "CARS", will sum all the car brands B10:C10 in this case, however, the selection there should be B10:C11 or A10:C11?

Hope its clear and again, txs a lot for your support.

​	2010​
CARS​	??​
PLANES​	??​

 

[Fluff]

There is noway that Excel can know if something is a car or a plane, so you need someway to tell it.

 

[mickeystanford_alumni]

There is noway that Excel can know if something is a car or a plane, so you need someway to tell it.

Hi Fluff, agree with that. There my question if there is any way to create an index there so that the formula takes the "CARS" value and gets all the values from there.
Thanxs once again

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	YEAR	MONTH	SALES	BRAND
2	2010	1	1	MERCEDES			MERCEDES	CARS
3	2010	2	15	MERCEDES			PORSCHE	CARS
4	2010	3	20	BOEING			BOEING	PLANES
5	2010	4	30	PORSCHE			JET	PLANES
6
7
8
9
10
11		2010
12	CARS	46
13	PLANES	20
Data

Cell Formulas
Range	Formula
B12:B13	B12	=SUMPRODUCT(($A$2:$A$5=B$11)*(XLOOKUP($D$2:$D$5,$G$2:$G$5,$H$2:$H$5)=A12)*($C$2:$C$5))