IF & SUMPRODUCT

[momentumons]

Hello! I am using this SUM PRODUCT formula that is working beautifully for me:

=SUMPRODUCT(--($B13:$B26="DISPLAY"),$D13:$D26,U13:U26)

However, I now have to add some criteria that says 'IF the outcome of the above is >(3%*U$29), then just put (3%*U$29) (ie 3% is the max).

Unfortunately I can't seem to do the normal "IF" thing with the SUMPRODUCT bit as the logical test.

I can currently make this work if I keep the SUMPRODUCT and add another line with the normal IF test, but i'd like to keep it all in one cell if possible (as then i'd have to hide the SUMPRODUCT one neatness and I am worried unseen errors might occur!)

Can you help? Thanks

 

[KRice]

Try this:
=MIN(0.03*U$29,SUMPRODUCT(--($B13:$B26="DISPLAY"),$D13:$D26,U13:U26))

 

[AhoyNC]

Maybe:

=MIN(0.03*$U29,SUMPRODUCT(--($B13:$B26="DISPLAY"),$D13:$D26,U13:U26))

 

[momentumons]

Genius! Thanks so much @AhoyNC . That is perfect.