index-match formula produces duplicates from SUMIFS data

[BSWJON]

Hi Folks,

I have the following conundrum which I could use your help with please!

1. titles in column G are duplicated as the values in column H are the same. The second value in column G should be R2 routine saw change.
2. I can't use the various online tips such as assigning random numbers to the dataset and looking those up instead, as the dataset is produced using a SUMIFS formula which adds values with like categories in another table.

See snips:

This is from the 'data' tab:

Any help will be greatly appreciated

Jon

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post a sample of your data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[BSWJON]

It says 365, however I thought 365 was the online version. In any case, it is the desktop version on windows 10.

WHich portion of the data would be most useful for solving this issue?

Thanks for your help

 

[Fluff]

Ok, if you remove the formulae in G21:H25 & put this in G21

=INDEX(FILTER(SORT(A3:C200,3,-1),{1,0,1}),SEQUENCE(5),{1,2})

 

[BSWJON]

Ok, if you remove the formulae in G21:H25 & put this in G21

=INDEX(FILTER(SORT(A3:C200,3,-1),{1,0,1}),SEQUENCE(5),{1,2})

Thank you for this- this works perfectly for the weekly range.

If I copy the formula into G13, and change the formula to use column number 1, it spills into column H.:

Any further ideas?

XL2BB:

Downtime Input- Rosens.xlsm
	B	C	D	E	F	G	H	I
1	May-21	31/05/2021	2021
2	Monthly	Weekly	Yearly
3	255.00	40.00	295.00			Yearly
4	20.00	0.00	20.00			Area	Amount	Percentage
5	150.00	0.00	150.00			R2 Machine Setup	485.00	18.14%
6	210.00	75.00	285.00			R2 Cleaning	365.00	13.66%
7	90.00	0.00	90.00			R1 Cleaning	295.00	11.04%
8	28.00	40.00	68.00			R1 Machine Setup	285.00	10.66%
9	60.00	0.00	60.00			R2 Out of Routine Saw Change	190.00	7.11%
10	125.00	0.00	125.00
11	0.00	0.00	0.00			Monthly		May
12	0.00	30.00	30.00			Area	Amount	Percentage
13	200.00	165.00	365.00			R2 Cleaning	R2 Cleaning	#VALUE!
14	80.00	55.00	135.00			R2 Machine Setup	R2 Machine Setup	#VALUE!
15	35.00	0.00	35.00			R2 Routine Saw Change	R2 Routine Saw Change	#VALUE!
16	395.00	90.00	485.00			R1 Machine Setup	R1 Machine Setup	#VALUE!
17	65.00	0.00	65.00			R2 Clear Rejects	R2 Clear Rejects	#VALUE!
Graphs & Top 5

Cell Formulas
Range	Formula
B3:B17	B3	=SUMIFS(Data!C:C,Data!H:H,'Graphs & Top 5'!B$1,Data!B:B,'Graphs & Top 5'!A3)
C3:C17	C3	=SUMIFS(Data!C:C,Data!I:I,'Graphs & Top 5'!C$1,Data!B:B,'Graphs & Top 5'!A3)
D3:D17	D3	=SUMIFS(Data!C:C,Data!J:J,'Graphs & Top 5'!D$1,Data!B:B,'Graphs & Top 5'!A3)
G5:G9	G5	=INDEX($A:$A,MATCH(H5,$D:$D,0))
H5	H5	=LARGE($D$3:$D$200,1)
I5:I9	I5	=H5/SUM($D$3:$D$200)
H6	H6	=LARGE($D$3:$D$200,2)
H7	H7	=LARGE($D$3:$D$200,3)
H8	H8	=LARGE($D$3:$D$200,4)
H9	H9	=LARGE($D$3:$D$200,5)
I11	I11	=B1
G13:H17	G13	=INDEX(FILTER(SORT(A3:C200,3,-1),{1,0,1}),SEQUENCE(5),{1,1})
I13:I17	I13	=H13/SUM($B$3:$B$200)
Dynamic array formulas.

 

[Fluff]

For monthly use

=INDEX(SORT(A3:B200,2,-1),SEQUENCE(5),{1,2})

 

[BSWJON]

Amazing, thanks for this. DIdn't think there was going to be a way to do this but you've proven me wrong.

Thanks again

 

[Fluff]

You're welcome & thanks for the feedback.