Sum a range of values only if it has certain value

[Closed]
Report
Posts
2
Registration date
Thursday April 25, 2013
Status
Member
Last seen
April 26, 2013
-
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
-
Hi,

I need a formula to sum the values in a column but only for the cells in the next column that have a specific value. In my case I have range of rainfall data for 30 years in one column with the their date in the next column. I need to sum up the value based on the months.
date is in the 19810101 format YYMMDD all in one column.
For example,
A ------------- B
19810101 ------- 1
19810102 ------- 0
19810103 ------- 22
19810104 ------- 0
19810105 ------- 10
19810106 ------- 5
19810107 ------- 2
19810108 ------- 0
19810109 ------- 0
19810110 ------- 23

I need to do that for the entire data range (10957 cells)

2 replies

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
Hi Suranjith,

I have tried the following, check if this helps:

I have changed the dates from the below mentioned format:

19810101

To:

1/1/1981

Using the formula:

=VALUE(LEFT(A2,4)&"/"&RIGHT(LEFT(A2,6),2)&"/"&RIGHT(A2,2))

Then, using the formula:

=SUMIF($E$2:$E$60,H2,$B$2:$B$60)

I have calculated the Rain in CMs for the corresponding month.

You can download the sample sheet from the below mentioned link:

http://speedy.sh/HUpUK/RaininCMs.xlsx

Please revert for clarification.
Posts
2
Registration date
Thursday April 25, 2013
Status
Member
Last seen
April 26, 2013

Thanks Zohaib for the effort. sounds interesting. Actually I too tried to resolved it. I used the excel function "Text to column" to arrange the format of the date and then break the date into different columns with YY | MM | DD and then refer to the Months column only. It worked for me. Anyway Thank you so much for the kind concern over my inquiry.. cheers
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
Hi Suranjith,

Thank for you kind response. I am glad to know you found my method interesting. Please feel free to get back to us if you have any further queries.