SUMIF and AVERAGEIF two separate columns, only picking the greater value between the two column.

[floW_5]

Using this sample below, the two formula I've been trying and playing around with from multiple google/yt search points to this, which does not calculate the intended return I am trying to achieve. My goal is trying to get the same result as what is in F13 (SUM) and F14 (Average), without the use of the Helper column I added to manually reference which value the sum and average formula "should" be pulling from Column C and D [Current:Previous].

More specifically, using only ONE value that is greater between column C & D in each row and only adding those values up to sum and average. If both column are equal, return will just use one of the two columns. If blank, it would technically use the other column with the value instead.

I have seen many similar question online but believe me when I say this, I've been on the google hunt the past few days trying different solutions and unless I am just not following the correct conditions this should be such a simple formula right?

Sample.xlsm
	A	B	C	D	E	F
1	Group	Category	Current	Previous		Helper using MAX()
2	A	One	20.09%	26.12%		26.12%
3	B	One		64.72%		64.72%
4	C	Two	40.37%	40.37%		40.37%
5	D	One	35.73%	35.73%		35.73%
6	E	Two	60.00%			60.00%
7	F	Two		20.00%		20.00%
8	G	One	16.59%			16.59%
9	H	One		60.67%		60.67%
10	I	One	42.02%	29.87%		42.02%
11	J	One	28.07%			28.07%
12
13	Not intended values with SUM() and AVERAGE() >>>		172.32%	Intended values to return >>>	SUM=	394.29%
14			34.46%		AVERAGE=	39.43%
Sample Data

Cell Formulas
Range	Formula
F2:F11	F2	=MAX(Table1[@[Current]:[Previous]])
C13	C13	=SUM(IF(Table1[Previous] >= Table1[Current], Table1[Previous], Table1[Current]))
C14	C14	=AVERAGE(IF(Table1[Previous] >= Table1[Current], Table1[Previous], Table1[Current]))
F13	F13	=SUM(F2:F11)
F14	F14	=AVERAGE(F2:F11)

Side question, Is it possible to turn these two formula to work with a dynamic filtered table without the use of any volatile function? I have already learnt on how to achieve this with a helper column calculating subtotal. But Is this even possible without the use a helper column and not use volatile functions such as offset?

 

[hagia_sofia]

How about this:

=SUM(MAP(C2:C11,D2:D11,LAMBDA(a,b,IF(a>b,a,b))))

=AVERAGE(MAP(C2:C11,D2:D11,LAMBDA(a,b,IF(a>b,a,b))))

 

[Phuoc]

It worked for me:

Book1
	A	B	C	D	E	F
1	Group	Category	Current	Previous		Helper using MAX()
2	A	One	20.09%	26.12%		26.12%
3	B	One		64.72%		64.72%
4	C	Two	40.37%	40.37%		40.37%
5	D	One	35.73%	35.73%		35.73%
6	E	Two	60.00%			60.00%
7	F	Two		20.00%		20.00%
8	G	One	16.59%			16.59%
9	H	One		60.67%		60.67%
10	I	One	42.02%	29.87%		42.02%
11	J	One	28.07%			28.07%
12
13	Not intended values with SUM() and AVERAGE() >>>		394.2933%	Intended values to return >>>	SUM=	394.2933%
14			39.4293%		AVERAGE=	39.4293%
Sheet1

Cell Formulas
Range	Formula
F2:F11	F2	=MAX(C2:D2)
C13	C13	=SUM(IF(C2:C11>=D2:D11,C2:C11,D2:D11))
C14	C14	=AVERAGE(IF(C2:C11>D2:D11,C2:C11,D2:D11))
F13	F13	=SUM(F2:F11)
F14	F14	=AVERAGE(F2:F11)

 

[floW_5]

Sample.xlsm
	A	B	C	D	E	F
1	Group	Category	Current	Previous		Helper using MAX()
2	A	One	20.09%	26.12%		26.12%
3	B	One		64.72%		64.72%
4	C	Two	40.37%	40.37%		40.37%
5	D	One	35.73%	35.73%		35.73%
6	E	Two	60.00%			60.00%
7	F	Two		20.00%		20.00%
8	G	One	16.59%			16.59%
9	H	One		60.67%		60.67%
10	I	One	42.02%	29.87%		42.02%
11	J	One	28.07%			28.07%
12
13	Not intended values with SUM() and AVERAGE() >>>		172.32%	Intended values to return >>>	SUM=	394.29%
14			34.46%		AVERAGE=	39.43%
15
16	Phuoc's formulas >>>	172.32%		hagia_sofia's formulas >>>		172.32%
17		34.46%				34.46%
Sample Data

Cell Formulas
Range	Formula
F2:F11	F2	=MAX(Table1[@[Current]:[Previous]])
C13	C13	=SUM(IF(Table1[Previous] >= Table1[Current], Table1[Previous], Table1[Current]))
C14	C14	=AVERAGE(IF(Table1[Previous] >= Table1[Current], Table1[Previous], Table1[Current]))
F13	F13	=SUM(F2:F11)
F14	F14	=AVERAGE(F2:F11)
B16	B16	=SUM(IF(C2:C11>=D2:D11,C2:C11,D2:D11))
B17	B17	=AVERAGE(IF(C2:C11>D2:D11,C2:C11,D2:D11))
F16	F16	=SUM(MAP(C2:C11,D2:D11,LAMBDA(a,b,IF(a>b,a,b))))
F17	F17	=AVERAGE(MAP(C2:C11,D2:D11,LAMBDA(a,b,IF(a>b,a,b))))
Press CTRL+SHIFT+ENTER to enter array formulas.

I know you guys ain't fooling around, I am literally copying and pasting the exact formulas you guys just posted and It is still returning the same unintended values. WHAT IS GOING ON?

 

[floW_5]

I figured out something really stupid why my original formula wasn't returning the correct values even though my original formula was correct.

After reviewing Phouc and hagia formulas, it was pretty much the same equation as in the op with the exception Hagia's formula really just simplified the formula to write up (thank you hagia). I then started looking up my options and settings with my current sheet thinking it must of been a setting I changed that could supposedly alter the way things are suppose to be calculated (if that is a thing). Clearly I was just overthinking it because I just randomly click on one of the so called blank cell and press the Delete button..... Guess what the results started changing for each blank cell I press delete on until it brought the real true verdict.

Despite those blank cells being empty to our eyes, apparently there was invisible value in those cells that caused the miscalculation. I am not sure if this is a first time thing or it is actually quite common with people on their spreadsheet. I guess its something for me to keep and eye out if something like this happens again. Thought I'd address that just incase someone else might come across a formula that they know is 100% correct but it is still returning a different result.

Either way It is fix now and I thank both Phouc and Hagia posting both working formulas. Thank you guys.

 

[hagia_sofia]

Thanks for extensive feedback and the most important thing is that the problem has been addressed...