# Sum a range of values only if it has certain value

Closed
Suranjith Posts 2 Registration date Thursday April 25, 2013 Status Member Last seen April 26, 2013 - Apr 25, 2013 at 07:17 AM
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 - Apr 26, 2013 at 06:21 AM
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 responses

Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Apr 26, 2013 at 05:18 AM
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.