How To Sum the Excel Cells with Different Conditions?

Shasun

New Member
Joined
Jun 22, 2011
Messages
3
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Shasun

New Member
Joined
Jun 22, 2011
Messages
3
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,036
Messages
5,508,922
Members
408,701
Latest member
Ucchik7

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top