How to split a total value into separate values?

Shaikk

Hello,

Greeting of the day!

I want to split the total 'Planned Labour hours' value into separate values based on F.start and F.end dates as month on month basis. we have 'Planned Labor Hours' in destination sheet and this data is pulled from another source sheet. The Planned Labor Hour' in destination sheet currently showing total number by month. Please find below screenshots for reference.

The frist image is from destination sheet.

The second image is from source sheet.

I need a split number of 60 in the month of November. In the destination sheet, we woluld like to have Planned Hours in the month of November. It would be great if you can help.

Thanks

Shaik

Genevieve P.

Hi @Shaikk

If I'm understanding you correctly, you're currently using a SUMIFS statement to SUM together the numbers in your source sheet for a total in your destination sheet, but instead you want to see the separate values (50, 10).

If so, instead of using SUMIFS, you can use JOIN(COLLECT. This will join together your values into one cell, based on the criteria you put in the COLLECT function.

For example:

=JOIN(COLLECT({Planned Hours}, {Date Column}, IFERROR(MONTH(@cell), 0) = 11, {Date Column}, IFERROR(YEAR(@cell), 0) = 2021), ", ")

The comma at the end of the JOIN ", " identifies how you want the values to be separated. You can adjust this to be anything, such as a dash or a space.

Let me know if this works for you, and if I've understood what you're looking to do.

Thanks!

Genevieve