CountIFS and sequencing: Weather example

[ROjer9]

Hi,

I currently have a sheet with two columns, column A is the date and column B is the weather (rain, sun, snow, cloudy)

Row 1 are the headings Date, and Weather

I logged the weather for each day using either rain, sun, snow or cloudy for the calendar month of january.

If consecutive dates had the same weather i.e rain, i would like to count that as one occurance of rain.

What i would like to produce is a weather report for that month of how many occurances (again an occurance could be multiple days) of each weather happened in that month, and the average number of days the weather lasted for per occurance.

E.g.

1/1 rain

2/1 rain

3/1 rain

4/1 cloudy

5/1 rain

6/1 cloudy

7/1 rain

8/1 rain

3 occurances of rain (3 days, 2 days, 1 day) with an average of 2 days per occurance of rain

2 occurances of cloudy, with an average of 1 day per occurance

Thank you in advance

Cheers

Dan

[#2 · Accepted solution]

@ROjer9 

See the attached demo workbook.

[#3]

What an absolute hero! Thank you so much!

The next question is:

That is for one town, if i have two cities, and want to combine output across two towns;

Town 1 town 2
Rain rain
Rain sun
Sun sun
Rain rain
Rain rain
Sun sun
Sun sun
Rain sun

So the output is
5 occurances of rain, avg 1.6days
4 occurances of sun, avg 2days

How would i do that?

Cheers

Dan

[#4]

@ROjer9 

You can simply add the days and occurrences together. See the sheet "Two Towns" in the attached workbook.

[#5]

Thank you.

If town 1 doesnt ever have Snow, but town 2 does, the UNIQUE formula wont pick up 'SNOW' from from its search of just one column. Is there a way of taking unique values from multiple columns?

Ive seen the use of FLATTEN, but my version of excel (i use the one from one drive) doesnt have FLATTEN. Is there another way?

Thanks for all your help.

Cheers

Dan

[#6]

@ROjer9 

Perhaps it would be easier to create a fixed list of all possible weather types.

See the attached version.