Require day of maximum amount calculated by sumif

[Vivek2705]

Hii..

I have Column A & Column B as my original data.

the output in D column is calcuated by sumif day-wise

the maximum amount of 203 is on wednesay.

I want wednesday as my final output in single cell without helper columns C & D.

Thank you.

[jason.b75]

It can be done with one helper column, but I don't think it is possible to eliminate both of them.

Assuming that C1 contains

Please Login or Register  to view this content.

, this array formula will get your result.

Please Login or Register  to view this content.

...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.

[AliGW]

Please attach a copy of the workbook.

[AliGW]

Jason - in this old thread I think you did something similar with AVERAGE: https://www.excelforum.com/excel-for...t-average.html

Can this not be done in one?

[Fluff13]

Another option
In C1 copied down

Please Login or Register  to view this content.

and then

Please Login or Register  to view this content.

[jason.b75]

Ali, the old thread that you refer to is working with clusters of consecutive cells, in this example they are not consecutive.

Also, the dates need to be manipulated in some way in order to group the different dates by weekday. As with countif(s), etc, subtotal will only accept ranges, not values.

I was wondering if it might be possible with a bit of MMULT wizardry, but if it is, it's not something that I have been able to figure out.

[AliGW]

Ah, OK - yes, there may be an MMULT solution, but it's beyond me, too.

[jason.b75]

I've been thinking this one over while I've been doing other things, the only way that I can see to solve this with a single formula is to take a novice approach and nest over 30 simple functions.

Please Login or Register  to view this content.

I wouldn't want to change that every time a new row of data is added

[jason.b75]

And immediately after posting the aforementioned novice formula, I have a eureka moment.

Please Login or Register  to view this content.

Array confirmed with Ctrl Shift Enter.

[AliGW]

Brilliant, Jason! One for the collection. I hope the OP will come back and see this.

[jason.b75]

Thanks for the rep, Ali and Fluff!

@Fluff, I'm not convinced that I fully understand how it works either I've attached a copy of my test file with some pointy arrow things and a few labels to try and illustrate how I think that the data is manipulated by MMULT. My explanation is far from that which would be expected for any kind of college course though.

[Fluff13]

Thanks for that Jason, certainly clearer now

[Vivek2705]

Thanks for the rep, Ali and Fluff!

@Fluff, I'm not convinced that I fully understand how it works either I've attached a copy of my test file with some pointy arrow things and a few labels to try and illustrate how I think that the data is manipulated by MMULT. My explanation is far from that which would be expected for any kind of college course though.

Wonderful Jason!! Thanks for the solution...