Iterating Sum If or other ...

[asselby]

Hi,

There are a live set of orders and helis for each plant every release day of the week. Some plant's days are not changed very often (and are therefore sometimes not on the 'log' for months) but will always have a defined number of orders and helis.

For each week in the year (weeknum) I need to capture the live number of orders and helis for each release day
Some plants and days are exported more than others. In the example data, for instance, the latest sunday release day at plan DN53 would have been on 05/06/20 but all other days have been exported more recently.

The week 43 live tab would therefore show all DN53 Mon-Sat from 20/10/2020 and sun orders and helis from 05/06/2020.

The formula must return (in the live tab) the latest orders and helis for each release day of each plant. This will be captured weekly (for each financial weel)- 'live' tab will show a record of orders and helis for every release day and plant for each week (weeknum) of the year.

Example data:
In 'log' tab, shows raw data of exported orders and helis for each plant and each release day
'Live Tab'- where the formula must return number of helis and orders (every week end a new weeknumber will be entered with corresponding list of plants and release days, we must therefore be able to copy the formula down for each new week)

Hope this makes sense

Thanks

[Limor_OP]

Hi
can you fill in manually the live tab with the expected results, and explain how do you get to this number?
it's a bit hard to follow your explanation.

[asselby]

Hi

I have filled in the first part of the Live tab with correct numbers and noted the data that is missing from the example log tab.

The number of Helis and orders per Plant/release day (each plant and release day will always have a number of orders and helis) will always be the latest ones in the log.
For example,plant DN14 has many logs for the same release day but the live tab will always only show the latest for that specific release day.

As week 43 passes, more logs will be captured and in week 44 it may be that DN22 Thu release day has a new number of helis and orders, so this will be captured in 'live' under week 44.
However, DN22 Fri may not have any new logs so the latest release day Friday log will be captured for week 44 as that is hat is currently live (even though that may have a date from three months ago, it is still the latest for that release day and plant).

Thanks

[bebo021999]

For Helix, row 2:

Please Login or Register  to view this content.

Copy to next cell for Orders, change Log!$I$2:$I$122 to column H

Drag both down

[asselby]

Thank you so much! That seems to have worked perfectly!
Could you explain in simple terms how the formula works?

Thanks

[bebo021999]

Let start with 3 condition ranges:

1/(Log!$C$2:$C$122=$A2)/(Log!$D$2:$D$122=$B2)/(Log!$F$2:$F$122<=$C2)

go thru each row to check, if all match, returns 1/1=1, else 1/0=#DIV/0
For instance
line 1: 1
line 2: #/DIV/0
line 3: 1
line 4: #/DIV/0

Then LOOKUP(2,Condition_range, result_range) match the last "1" found , then returns value in result_range

[Limor_OP]

Hi,
Thanks, it helped understanding your logic.

I still don't understand the blanks you would like to display, in some of them there is data matching to criteria so why would you like to leave that empty?

Use this in D2 down and across for the info requested:
=IFERROR(INDEX(Log!H$1:H$101,MATCH(2,1/(Log!$D$1:$D$101=$B2)/($C2>=Log!$F$1:$F$101)/(Log!$C$1:$C$101=Live!$A2))),"")

Let me know the logic behind the empty lines and I will try to adjust...


Ok, Bebo has beat me to it