Log In
Register
Home
March 10, 2016
Offline
Hi,
Attached File
Thanks for the help
I need to calculate a certain line in it and need the Excel to know how to take the relevant line -
If it's Yom Kippur
So give me the summary of the hours of the day before - the eve of Yom Kippur
And the day after-
How to make Excel calculate the sum of column F
In the line above and in the line below - for a certain figure ??
Here in the attachment a summary should be obtained - 8.5
Since I have to set it for all the holidays
I would love to get help defining a calculation from a different line
Then I will continue to define the same solution for the rest of the holidays.
Thank you!! Leah
July 16, 2010
Offline
Hi Lea,
It's a bit hard to follow your example as I'm not sure where you got 8.5 from because that's 3 days before Yom Kipur i.e. on the 13th September.
That said, why don't you try INDEX & MATCH to find Yom Kipur -1 e.g.
=INDEX(F2:F32,MATCH("Yom Kipur",G2:G32,0)-1)
Mynda
March 10, 2016
Offline
According to the example - the cells that Excel needs to sum up are F16: F18
I also need to summarize a line before Yom Kippur
And also a line after Yom Kippur
And the INDEX MATCH function only pulls out one statistic for me.
How can I set a summary for these cells?
I also need it to be done for all the holidays and not just for Yom Kippur -
How can this be defined?
Thank you for listening
And for the professional answer !!
Leah
July 16, 2010
Offline
Thanks for clarifying. You can use this formula:
=SUM(INDEX(F2:F32,MATCH("Yom Kipur",G2:G32,0)-1):INDEX(F2:F32,MATCH("Yom Kipur",G2:G32,0)+1))
Obviously for Rosh Hashana, you will need to modify the formula to this:
=SUM(INDEX(F2:F32,MATCH("Yom Kipur",G2:G32,0)-1):INDEX(F2:F32,MATCH("Yom Kipur",G2:G32,0)+2))
Mynda
March 10, 2016
Offline
Thank you Minda for the answer !!
The formula still does not work for me
I got 17 hours - it is not clear to me which cells he calculated
Attaches the file with coloring-
For every holiday I need the day before and the day after -
For example here in the file summarize all the orange cells
I would love to know how to do it?
Thank you for listening
And patience for my question
Leah
March 10, 2016
Offline
I wrote here now a long function -
In the yellow cell in the attachment
Is there a way to write it shorter?
Is there a possibility for Sukkot - do not define Sukkot 1 and Sukkot 2
And that he knew how to take Sukkot twice ??
Thank you!!! Leah
1 Guest(s)
