SOLVED

New Contributor

CountIFS and sequencing: Weather example

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

Cheers

Dan

5 Replies
best response confirmed by ROjer9 (New Contributor)
Solution

Re: CountIFS and sequencing: Weather example

See the attached demo workbook.

Re: CountIFS and sequencing: Weather example

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

Re: CountIFS and sequencing: Weather example

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

Re: CountIFS and sequencing: Weather example

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?