How To Sum the Excel Cells with Different Conditions?

[Shasun]

Hi all!

I would like to sum the cell values from D7:M7. Initially I have unique values for those cells but I want to add those cells based on my criteria, because each cell has different conditions to satisfy.

For example, D7=IF(D7<=W7,D7,W7); E7=IF(E7<=W8,E7,W8) etc.,

D7, E7 - cells with unique values
W7, W8 - conditions for those cells

One way to do this is to check the condition for each cell separately and then have to add it all together. But I wonder is there any other way (using one formula/method) to check the condition for each cell and if it satisfies (either true or false) and to sum.

Pls, let me know if you have any idea to solve this issue. Thanks in advance.

 

[Robert Mika]

Could you post an example of your data?

 

[Shasun]

Re: How To Sum the Cells in Excel with Different Conditions?

Hi...

Thanks for your response. herewith I posted my query for your reference.


Requirement
Tank1 Tank2 Tank3 Tank4 Tank5 Tank6 Tank7 Tank8 Tank9 Tank10
3L 3L 6 L 6 L 3 L 6 L 3 L 3 L 3 L 6 L

There are 10 different tanks with overall capacity/storage of 24L and each having different rate of refilling rate (which is given above).

Ex: Tanks such as 1, 2, 5, 7, 8 and 9 has refilling rate of 3Litre per minute and Tank 3, 4, 6 and 10 has the refilling rate of 6 L per min.

Time Tank1 Tank2 Tank3 Tank4 Tank5 Tank6 Tank7 Tank8 Tank9 Tank 10 Total
00:01 2 6 1 6 4 3 8 7 12 6
00:02
00:03


In the above table, first row shows the individual tank demand for the beginning of an hour (water that needs to be filled in tanks to reach its net capacity). I want to calculate the total demand based on the tanks refilling capacity.

For example, tank 4 needs 4L (demand) of water to reach its total net capacity. This can be filled in less than 1 min with its given refilling rate. Similarly the tank 7 needs 8 L, but it is able to refill at the rate of 3L per minute only. Even though it demand shows as 8L, the actual demand for that period is 3L only. This condition applies to other tanks also.

For each cell (tank), I’m able to calculate demand by using the following formula:

If (Tank_demand <= Refilling_rate) then Tank_demand else Refilling_rate

Instead of using this formula for each cell to calculate the demand separately and adding it together, I am looking for single formula/method (macros etc) to check the condition for every cell and to calculate the overall demand.

FYI: Without using this condition the total demand from tank1-10 is 55L. By using the refilling rate condition (for each and every cells individually), I calculated the demand as 31L.

Pls, let me know if you have any idea to solve this issue.