Period Data Collation

[BullseyeThor]

Hi All

I have a query I have been racking my brain but I just can't get round it.

I have attached a sheet with some date on and an example of what the outcome needs to be. I'm hoping someone can come up with the formula that I need to make it work, I just have a complete mental block.

Thanks all in advance

Example.xlsx

[arlu1201]

Will there always be 12 periods per status?

I am not very well versed with formulae but i can help you with a macro solution, if you want.

[e.isayev]

Cell E12 =SUM(--(IF(ISERROR(FIND($A$1:$A$9,D12,1)),0,1)+IF(ISERROR(FIND($B$1:$B$9,D12,1)),0,1)>=2)) An array formula

But in this example Period 10,11,12 shows as 1 because they include the word "Period1" But if you will use real numbers there will be no problem with that

[e.isayev]

Have a look to the file, You will understand what i mean

[TheExcelphile]

Hi!

Its just a simple matter of using COUNTIFS().

Here is your workbook with the formulas you need...Copy of Example.xlsx

Don't forget to click the little star to the left of this post if you feel I helped!

[arlu1201]

BullsEyeThor,

Do you need the values in D12:D23, G12:G23 and J12:J23 to be created by the formula too?

[e.isayev]

Hi!

Its just a simple matter of using COUNTIFS().

Here is your workbook with the formulas you need...Attachment 193076

Don't forget to click the little star to the left of this post if you feel I helped!

Yes... Another way of solving problem. Which is much more smarter than mine. Why I cant see easy way of solving problems

[BullseyeThor]

Thanks for the help all, I will be able to use the examples to make my worksheet much eaasier to manage.