Adding result if totals are equal

[Marvo]

Good morning,

I have the following simple formula that works out which is the greater amount, the first 9 cells or second 9 cells.

=IF(D5+F5+H5+J5+L5+N5+P5+R5+T5>V5+X5+Z5+AB5+AD5+AF5+AH5+AJ5+AL5,"F","B")

Unfortunately it returns "B" if the total are equal. What can I add to the formula to show if the totals are ever equal please? I'd probably have "E" as the result.

Many thanks.

[AliGW]

Try this:

=IF((D5+F5+H5+J5+L5+N5+P5+R5+T5)>(V5+X5+Z5+AB5+AD5+AF5+AH5+AJ5+AL5),"F",IF((D5+F5+H5+J5+L5+N5+P5+R5+T5)=(V5+X5+Z5+AB5+AD5+AF5+AH5+AJ5+AL5),"E","B"))

I'll bet this could be simplified with SUMIFS, but I'd need to see it in context.

[Sam Capricci]

this seems easier... =IF((D5+F5+H5+J5+L5+N5+P5+R5+T5)>(V5+X5+Z5+AB5+AD5+AF5+AH5+AJ5+AL5),"F","B")

[AliGW]

But it doesn't do what is required ...

[Marvo]

By all means Ali.

The formula is in AO5.

Can anything also be added to produce no result (blank cell) when no data is entered when the formula is copied down ? As in cell AO96.

[Sam Capricci]

Should've read it more closely, I see your point.

[AliGW]

Try this:

=IF(SUMIF($C$4:$T$4,"Pts",$C5:$T5)=SUMIF($U$4:$AL$4,"Pts",$U5:$AL5),"E",IF(SUMIF($C$4:$T$4,"Pts",$C5:$T5)>SUMIF($U$4:$AL$4,"Pts",$U5:$AL5),"F","B"))

[Glenn Kennedy]

... or

=IFERROR(LOOKUP(SUMIF($C$4:$T$4,"Pts",C5:T5)/SUMIF($U$4:$AL$4,"Pts",U5:AL5),{0,1,1.00000001},{"B","E","F"}),"")

[AliGW]

Nice idea!

[Marvo]

That works a treat Glenn, thank you and thanks to everybody else.

I'd just been playing with the =IF(OR(ISBLANK) function on Ali's formula and got it to work but yours is a shorter solution. Cheers.

[AliGW]

Thanks for the rep.