display sumifs items

[Rynke]

Hi,

I've been looking at this thread: http://www.excelforum.com/showthread.php?t=754662
I've a similar request, I just need the items from a sumifs function instead of sumif.

I have 4 criterias in my sumifs and need it displayed directly in cells, instead of a messagebox.

I'm not sure, if this is possible. Anyone you who can confirm if this is possible?

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[Rynke]

Hi Richard,

I've created an example, but I can't upload the workbook.
I hope I can explain with below pictures instead.

Below sheet contains the data:
Data sheet.jpg

On below sheet I'm using a sumifs function, to have the total amount of sales.
I would then like to have all the specifications, found in the sumifs, displayd in the yellow area.

Spec Sales.jpg

[FDibbins]

Rynke welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

From the sounds of it, you probably need to use the INDEX/SMALL/IF() ARRAY function, maybe something like this...

	A	B	C	D	E	F	G	H	I
1	Header1	header2	Header3	Amt		aa	ff	kk
2	aa	ff	kk	10		Sum	60
3	bb	gg	ll	20
4	cc	hh	mm	30
5	dd	ii	nn	40		Header1	header2	Header3	Amt
6	aa	ff	kk	50		aa	ff	kk	10
7	bb	ff	ll	60		aa	ff	kk	50
8	cc	gg	mm	70
9	dd	hh	nn	80
10	aa	ii	kk	90
11	bb	jj	ll	100
12	cc	ff	mm	110
13	dd	gg	nn	120

G2=SUMIFS(D:D,A:A,F1,B:B,G1,C:C,H1)

for the extract...
F6=IFERROR(INDEX(A:A,SMALL(IF(($A$2:$A$13=$F$1)*($B$2:$B$13=$G$1)*($C$2:$C$13=$H$1),ROW($A$2:$A$13)),ROWS($A$1:A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy down, then copy across as needed

[Rynke]

Hi FDibbins,

Thanks for the instruction how to upload a file. I hope my example is attached now

Correct me if I'm wrong, but does you aray formula include all results between two criterias?

/Rynke