Most Frequently Occurring Product (Index + Mode + Match)

Resolved · High Priority · Version 365

Wed 25 Mar 2020, 17:06 Delegate Tsudoi said...

Tsudoi has attended:
Excel PowerPivot course
Excel VBA Introduction course

Most Frequently Occurring Product (Index + Mode + Match)

Hi,
I'd like to achieve a formula that returns the most frequently occurring text (product) but with multiple criteria (Date and Product Type) without array formula.
I've found a good tip from the link below but it only does an half of what I need.

https://www.youtube.com/watch?v=pki8C60Diz0

With this tip in mind (or without if you know any other ways), please can you advise how to return the most frequently occurring text with multiple criteria?

Thanks,

Thu 26 Mar 2020, 16:26 Trainer Jens said...

RE: Most Frequently Occurring Product (Index + Mode + Match)

Hi Tsudoi,

Thank you for the forum question.

I cannot see this be done without an array. You need Excel to test each row in a list. I have seen the video and it was interesting that the Match function worked like it was an array.

The Sumproduct is an array function and can work with multiple test.

Sorry I am 99% sure it cannot be done without using arrays.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 26 Mar 2020, 17:09 Delegate Tsudoi said...

RE: Most Frequently Occurring Product (Index + Mode + Match)

Hi Jens, thanks for your reply. I'll need to find a way to work around it then. Sumproduct is a good function but unsure how I can use that to achieve what I want. I'll keep on trying..
Regards

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Generating Random Numbers

To generate a random number in Excel use the = RAND() function.

The value returned will always be between 0 and 1. To convert this to some other random value, you will need to multiply the result by the highest number you want to consider. For example, if you wanted a random number between 1 and 25, you could use the following code line:
= INT(25 * RAND()+ 1)

Since RAND() will always returns a value between 0 and 1 (but never 1 itself), multiplying what it returns by 25 and then using the Integer function INT on that result will return a whole number between 0 and 24.

Finally, 1 is added to this result, so that x will be equal to a number between 1 and 25, inclusive

View all Excel hints and tips