Formula: Identify & sum multiple date ranges that include given date

[MisterProzilla]

Hi there,

Not sure if the title of this question explains it very well...

I have members of staff who work 'fractions' (which are actually decimals, but never mind, HR). I need to identify which fraction they work on a particular date. Some work multiple fractions simultaneously. I need a formula that looks up a single date in a table of fraction date ranges, and sums the fraction values of all applicable/active date ranges on that date.

I have a fraction date range table in my Calendar sheet with start dates in column AA, End dates in column AB, and the fraction values in AC.

I have this formula which finds a single fraction, where E28 is the date I'm looking up:
=LOOKUP(2,1/(Calendar!AA35:AA39<=E28)/(Calendar!AB35:AB39>=E28),Calendar!AC35:AC39)

However, when multiple fractions are active, it only returns the lowest in the table. How would I modify this to sum the results where multiple fractions are active?


Thanks for reading, any help would be much appreciated

 

[AlanY]

try this

=SUMIFS(Calendar!AC35:AC39,Calendar!AA35:AA39,"<="&E28,Calendar!AB35:AB39,">="&E28)

 

[MisterProzilla]

try this

=SUMIFS(Calendar!AC35:AC39,Calendar!AA35:AA39,"<="&E28,Calendar!AB35:AB39,">="&E28)

Perfect

Thanks very much Alan

 

[AlanY]

you're welcome