IF Based on result of a SUMIF (built into formula)

[garypea123]

Hi,

I am trying to create a formula which will do a SUMIF but then provide a different result if the answer is less than needed (without creating another cell)

Q12 = =SUMIF($L$12:$L$17,P12,M12:M17)

However, as an Example I would like Q12 to return a result of 20 if the actual result is 20...

R12 = =IF(Q12<20,20,Q12)

I can do this easily by creating another cell and applying an if statement. But can I this built into Q12 formula so I do not need to create a new cell?

 

[Marcílio_Lobão]

garypea123, Good morning.

This formula works for you?

Q12 --> =MAX( SUMIF($L$12:$L$17,P12,M12:M17), 20)

I hope it helps.

Have a nice day.

Greetings from Brazil.

 

[garypea123]

Great, Greetings Brazil, from UK
This works for sure if I am going to return a specific number value....

However, if I would like to actually apply another fomula instead of a number.

I have updated an example below:

I would like Q12 to create a SUMIF from Table 1, however, of the result is less than 20 then I would like the it too look at Table 2 (which should be more than 20 in theory).

R12 = =SUMIF($L$12:$L$17,P12,M12:M17)
S12 = =SUMIF($L$12:$L$17,P12,N12:N17)

However, I would like to not have R12 and S12 in use, and the formula being driven purely by Q12.

Currently in Q12 I have manually put in what I would expect to see if the formula works

 

[Marcílio_Lobão]

garypea123,

I believe that this formula works for your new necessity.

Q12 -->

=IF(SUMIF($L$12:$L$17,P12,$M$12:$M$17)<=20, SUMIF($L$12:$L$17,P12,$N$12:$N$17), SUMIF($L$12:$L$17,P12,$M$12:$M$17))

Since you didn't say what should happen when the sum value is = 20, I am assuming in the formula that it is <= 20.

Can you work with this new formula?

I hope it helps.

 

[garypea123]

Thats perfect, and even more it makes complete logical sense that I am surprised with myself for not being able to write that myself (maybe just a bad day).

But yes, its perfect and definitely helps me moving forward.

 

[Marcílio_Lobão]

garypea123,

Thanks for feedback.
I feel glad to help you.

Have a nice day.