Return the most frequent text result in column J, only if the workplan category (column I), matches the column A.

[kennysmith1]

ex. "Business Office" (A2), the result in B2 would display "Omitted charges" as it is the most frequent text in column J for that workplan category. (Omitted charges 4x), (Data entry error 2x).
I have been trying to use index/match/mode functions with no success, as well as getpivot data functions.

 

[kennysmith1]

all good for now. Thanks very much for the help with this!

 

[kennysmith1]

hey man, had a separate question. is there a possiblility to do a sumifs to sum column AD, only if column V is the largest result for the specific key in column AN?

For example,if the key is "Epic WQ Breast Marker Supplies & Implants", I am looking for a formula that would return "HC Needle localization brevera standard breast stereotactic" and another formula that would return "$7,543.80". This is a little different from the formula you helped me with previously, as instead of the result being based on a mode, it is now based on the largest $ amount sum. Hope this makes sense?

 

[Eric W]

I've been away for a while. Since this is a new question, you should really open up a new thread. But while I'm here, I have a few ideas.

Making a formula to do this in Excel 2016 is nearly impossible. I won't say completely impossible since there are some very clever ways to do this, but any formula would be long and complicated. However, in Excel 365 (or even 2021), this would be pretty easy. Some of the newer functions are designed to do this kind of thing automatically.

A second option is to do this with a VBA macro. This would not be terribly difficult.

Finally, you might look into Pivot Tables. They are basically mini report writers, and can organize data like yours pretty simply. For example, taking your data:

Book1
	A	B	C	D	E	F	G
1	CDMCodeDescription	AdjustmentAmt	Key1			Row Labels	Sum of AdjustmentAmt
2	A	$ 6,845.30	Epic			C	$ 22,631.40
3	B	$ 1,012.83	Epic			G	$ 7,075.80
4	C	$ 3,771.90	Epic			A	$ 6,845.30
5	C	$ 3,771.90	Epic			K	$ 4,749.80
6	C	$ 3,771.90	Epic			E	$ 4,336.28
7	C	$ 3,771.90	Epic			H	$ 2,116.23
8	C	$ 3,771.90	Epic			F	$ 1,187.45
9	C	$ 3,771.90	Epic			J	$ 1,117.60
10	D	$ 813.05	Epic			I	$ 1,084.07
11	E	$ 1,084.07	Epic			B	$ 1,012.83
12	E	$ 1,084.07	Epic			D	$ 813.05
13	E	$ 1,084.07	Epic			Grand Total	$ 52,969.81
14	E	$ 1,084.07	Epic
15	F	$ 1,187.45	Epic
16	G	$ 3,537.90	Epic
17	G	$ 3,537.90	Epic
18	H	$ 2,116.23	Epic
19	I	$ 1,084.07	Epic
20	J	$ 1,117.60	Epic
21	K	$ 1,187.45	Epic
22	K	$ 1,187.45	Epic
23	K	$ 1,187.45	Epic
24	K	$ 1,187.45	Epic
Sheet2

It took less than a minute to get the pivot table on the right. You can find some online resources on how to use them.

Hope this helps!