Conditional SUM

[Saad_Khalil]

Hello all,

If anyone could clear my doubt. I want my table to add its values automatically after every hour. How can i do this in excel.

[#2]

Hi @Saad_Khalil 

As per my understanding, you need a calculation in which you want to sum the values after each interval of an hour. To achieve the requirements, you can use the NOW() function.

Based on the above, I have made the attached sample file.

Let's assume you have defined the hour's intervals in column B and amounts in column C.
To accomplish the desired results, you also need a starting time as a reference point that you will input in cell C4. You can either manually input the starting time or copy it from C1 and paste it as values into C4.

=IF(NUMBERVALUE(TEXT(NOW(),"HH:MM:SS"))>(NUMBERVALUE($C$4)+NUMBERVALUE(TIME(B8,0,0))),(D7+C8),0)

It is pertinent to note that since the NOW() function displays the current time, it, therefore, needs calculations to be refreshed. So please always press F9 before you take the results from column D.

Hope it will help.

Please check the attached sample file and let me know if it works for you.

Thanks

Tauqeer

[#3]

HI  @tauqeeracma,

Firstly I would like to thank you for replying. Brother I get what you have done here, but not quite what I was looking for. I will explain the scenario below.

Scenario:

I have some data in a table and at the end of the table there is a cell for the total of the values in the table. Normally when we change any value in the table the total is re-calculated right away. What I want is that the total is re-calculated after an interval of an hour, irrespectively if we change a value in the table or not.

I hope I was able to explain my scenario.

Regards 

Saad

[#4 · Accepted solution]

Hi @Saad_Khalil 

In that case, I think the best option is to put the calculation option as "Manual" and uncheck the 'Recalculate workbook before saving' option as mentioned below:

Update your table as you like, and change the values. The total of the table will remain the same.

Whenever you want to refresh your table, simply press the F9 key.

Please ensure that the option is set again as "Automatic" for other excel workbooks.

Thanks

Tauqeer

[#5]

Following.

[#6]

Thankyou very much @tauqeeracma.