Previous Topic Next Topic

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 replies

Answer 1 / 2
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.

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

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

Please revert for clarification.
0
Suranjith
Posts
2
Registration date
Thursday April 25, 2013
Status
Member
Last seen
April 26, 2013
Apr 26, 2013 at 05:55 AM
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
0
Answer 2 / 2
Zohaib R
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
 69
Apr 26, 2013 at 06:21 AM
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.
0