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 responses

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.
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
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.

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Clear cell contents based on value of another cell range using VBA
Tbonekiller -
Tbonekiller -

6 responses
Return Seq Values Until Blank value then return different set of seq values
lorieb63 -
TrowaD -

1 response
Fill adjacent cell with number from text answer
MitchP -
vcoolio -

20 responses
Excel function question
1234ah -
ac3mark -

3 responses