Sum the column value

[RohitDangi]

I have a process which will always be run on every Monday of the month. I have a excel sheet which have a 3 Columns, 'Balance', 'Status', 'Date'. I have to sum the value of the 'Balance' column based on the condition 'Status = Complete' and date = (1st day of the month to the last friday to the running monday(process run on every monday).
I have to complete this asap.
I'll be very grateful.

 

[sivagelli]

Three ways-
#1. Getting data from Excel to Collection and applying filter collection with condition: "Status='Complete' AND Date=AddDays(Today(), -3)" If your Date is in date format, the above condition usually works
#2. Apply Filter and Formulas in the excel sheet itself and get the cell value of Sum. This would require custom code for applying filtering on columns
#3. Using Data- OLEDB VBO, read the entire excel as database and execute query to get the data in to a collection for Sum operation

 

[rajatchandna]

if I have data in the collection.. how can I add all the values in a particular column & store in a data item?

 

[amlan Sahoo]

I have a process which will always be run on every Monday of the month. I have a excel sheet which have a 3 Columns, 'Balance', 'Status', 'Date'. I have to sum the value of the 'Balance' column based on the condition 'Status = Complete' and date = (1st day of the month to the last friday to the running monday(process run on every monday).
I have to complete this asap.
I'll be very grateful.

Hey its very easy filter read all values from excel to collection then use filter collection according to your filter condition then put a loop in the filtered collection add all column values...

To add column values initialize a data item with value as 0, each time yo will go through loop add the current value from collection to that data item and the save in the data item so It will be updated till end.

Let me know if any doubt.

@rajatchandna I hope your query also I have answered

 

[Narnia28]

Hi,
I have one query, if any one of you could help would be great.

I want to to the addition of column values and store it into the next cell. So far I am able to achieve only the addition part, that to partially. What I have done is, I have stored the addition into a data item. So is there any other way to do this.