Solved How to sum the excel cells w diff conditions?

June 22, 2011 at 08:40:58
Specs: Windows 7
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.,

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.


See More: How to sum the excel cells w diff conditions?

Report •


✔ Best Answer
June 22, 2011 at 19:32:15
While I would prefer that the next time you post your data you follow the instructions in the "How To" reachable via the link in my signature line, I think I figured out what your data is supposed to look like.

However, I believe the answer you are looking for is 33, not 31.

Assuming your data looks like this, the formula following the data will return 33 in L5.

   A     B     C     D     E     F     G     H     I     J     K      L
1      Tank1 Tank2 Tank3 Tank4 Tank5 Tank6 Tank7 Tank8 Tank9 Tank10
2        3     3     6     6     3     6     3     3     3     6
3
4 Time Tank1 Tank2 Tank3 Tank4 Tank5 Tank6 Tank7 Tank8 Tank9 Tank10 Total
5 0:01	 2     6     1     6     4     3     8     7     12     6     33
6 0:02
7 0:03

=SUM(MIN($B$2,B5),MIN($C$2,C5),MIN($D$2,D5),MIN($E$2,E5),MIN($F$2,F5),
MIN($G$2,G5),MIN($H$2,H5),MIN($I$2,I5),MIN($J$2,J5),MIN($K$2,K5))

Drag this down Column L.

This formula will work if there is a "0" in any of the cells you are summing, (e.g. Row 5) but it will not work if there is a blank cell.

If you will have blank cells, you'll need to use:

=SUM(IF(B5<>0,MIN($B$2,B5),0),IF(C5<>0,MIN($C$2,C5),0), etc.)

That's because the MIN function will ignore the blank cells in each range and return the value from Row 2 whenever it ignores the blank.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
June 22, 2011 at 09:51:48
What version of Excel are you using?

2007 and later have a SUMIFS function that might work for you:

http://office.microsoft.com/en-us/e...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 22, 2011 at 14:13:13
Hello Derby

Thanks for your response. I'm using Excel 2007 version and my query for your view.


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.


Report •

#3
June 22, 2011 at 16:17:15
I'll assume you overlooked the bold blue sentence at the bottom of my last post.

If you follow the instructions found by clicking on that line and repost your data, we'll see what we can do.

Here it is again:

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 22, 2011 at 19:32:15
✔ Best Answer
While I would prefer that the next time you post your data you follow the instructions in the "How To" reachable via the link in my signature line, I think I figured out what your data is supposed to look like.

However, I believe the answer you are looking for is 33, not 31.

Assuming your data looks like this, the formula following the data will return 33 in L5.

   A     B     C     D     E     F     G     H     I     J     K      L
1      Tank1 Tank2 Tank3 Tank4 Tank5 Tank6 Tank7 Tank8 Tank9 Tank10
2        3     3     6     6     3     6     3     3     3     6
3
4 Time Tank1 Tank2 Tank3 Tank4 Tank5 Tank6 Tank7 Tank8 Tank9 Tank10 Total
5 0:01	 2     6     1     6     4     3     8     7     12     6     33
6 0:02
7 0:03

=SUM(MIN($B$2,B5),MIN($C$2,C5),MIN($D$2,D5),MIN($E$2,E5),MIN($F$2,F5),
MIN($G$2,G5),MIN($H$2,H5),MIN($I$2,I5),MIN($J$2,J5),MIN($K$2,K5))

Drag this down Column L.

This formula will work if there is a "0" in any of the cells you are summing, (e.g. Row 5) but it will not work if there is a blank cell.

If you will have blank cells, you'll need to use:

=SUM(IF(B5<>0,MIN($B$2,B5),0),IF(C5<>0,MIN($C$2,C5),0), etc.)

That's because the MIN function will ignore the blank cells in each range and return the value from Row 2 whenever it ignores the blank.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
June 23, 2011 at 06:39:16
Thanks a lot for your help Derby !!! It works for me now.


P.S. next time if i post any formula/table, I will certainly follow the instructions in the "How To post data or code"


Report •

Ask Question