How do I write a SUMIF formula for pricing?

1

SAMPLE IMAGE HERE

I have an excel program used to price out paint and the "tints" used in the paint formula. What I'm trying to achieve is:

  1. If the sum of cells B54:H54 is =0 then cell I54=0
  2. If the sum of cells B54:H54 has a range of 1 to 30 then I54=30 as in a (30 tint min rule unless there is 0 tints then =0)
  3. If the sum of cells B54:H54 exceeds 30 then it's whatever the value comes out to. After looking online I assume I need to use the SUMIF function, I'm just not certain on how to setup the criteria.

Jared

Posted 2016-08-03T14:22:07.560

Reputation: 27

Answers

0

SUMIF is useful for conditional addition (e.g. sum only numbers which are less then 10), you need to use SUM and IF:

=IF(SUM(B54:H54)=0,0,IF(SUM(B54:H54)<30,30,SUM(B54:H54)))

Máté Juhász

Posted 2016-08-03T14:22:07.560

Reputation: 16 807

That is absolutely PERFECT! Thank you very much for the quick response. This working example helps me understand how I can edit my other working forms. – Jared – 2016-08-03T14:40:07.333

Asked: 2016-08-03T14:22:07.560

Viewed: 40 times

Active: 2016-08-03T15:01:33.217