Chained Vlookups or Sumifs

[Mehdi Shahsavari]

I work with precipitation data from a station. the data is presented like this:

in first column i have year-month like 1936-01 and in the quantity of rain in each day is presented in the corresponding row. (i have uploaded a part of the table in attached file)

i want to create a table in which years are in the first column and months are in the first row and sum of rainfall in each month is below the corresponding month in front of corresponding year.(as shown in picture)

  is it possible to make such a table with chained vlookups or sumifs? or i should write a vba code for it?

[#2]

Hi Mehdi,

That could be like

=SUMPRODUCT((LEFT($A$2:$A$30,4)=$A35)*(RIGHT($A$2:$A$30,2)=TEXT(B$34,"00"))*$B$2:$AF$30)

ranges are in attached file, and if you change text within your data on numbers, other words "-" on 0.

[#3]

Hi Mehdi,

The best way is to use Power Query (Aka Get & Transform Data in Excel 2016) to create a proper data set to be able to summarize it by using PivotTables tool which is the best tool to summarize data.

I've done this for you, please find the attached file to see it.

NOTE: if you don't have Excel 2016, you have to download and install the Power Query add-in.

Regards

[#4]

Hi Haytham,

Pivoting could be done directly in query. e.g. as attached