# Vertical total of Horizontal Bar Chart value or total number between lots of range dates

1. ## Vertical total of Horizontal Bar Chart value or total number between lots of range dates

Hi guys I am new to the forum,

Not sure if the title makes any sense but here is what I am trying to do.

I am basically generating a schedule to show how many workers I would need for all of on going projects at a given date(during 3~4 week duration). I have generated a horizontal bar chart with project start and end date and have number of workers labeled on the right side.

Is there a way to show how many workers are needed on each given day on a chart? It would be like taking the sum of vertical values from a horizontal chart. I am having trouble because the projects have different range of dates therefore the number of workers needed will have to total between various start date >= <=end date.

It doesn't necessarily have to be a bar chart, it could be a linear chart reflecting range and count. As long as I can see the count for each day for the next few weeks. I also tried to create a pivot using sumifs with date range but the number generated is not what I was looking for.

Not sure if how I explained makes sense.

Ex.

8/25 would show FTE = 8
8/26 - 8/27 FTE = 16
8/28 -09/09 FTE = 21
09/10 - 09/11 FTE = 16
09/12 - 09/15 FTE = 13
09/16 - 10/14 FTE = 8
and so on....

This is confusing to explain.

Please refer to the orange bar chart

Thanks for the help!!!

2. ## Re: Vertical total of Horizontal Bar Chart value or total number between lots of range dat

The chart will not do the subtotaling for you, but a chart can certainly be used to visualize subtotals that are calculated in the spreadsheet. It's really just a question of what you are trying to subtotal and what formula you want to use to perform that subtotal. Unfortunately, I am not understanding how you want to perform the subtotals (or I am understanding, but your example values are not taken from the sample sheet).

Assuming I understand what I am seeing, a subtotal of workers needed for a specific calendar day could be calculated using a SUMIFS() function:
1) Enter 44068 (or 8/25/2020) into Y6. Enter =Y6+1 into Y7 and copy down as far as needed.
2) Into Z6, enter =SUMIFS(\$N\$6:\$N\$23,\$C\$6:\$C\$23,"<="&Y6,\$E\$6:\$E\$23,">="&Y6). Copy/paste/fill down as far as needed.

That should give you a total of the "workers" in column N for each calendar day (though, again, I note that this gives different answers in this spreadsheet than you've indicated in the OP). I can then create a new chart (line) that shows the total number of workers needed for each calendar day.

That's my best guess. If that isn't correct or doesn't help you find a correct formula, then you will need to explain better what you need to do.

3. ## Re: Vertical total of Horizontal Bar Chart value or total number between lots of range dat

Hi MrShorty,

Thank you for the answer. I will try to clarify.

The example values FTE are taken form N column Worker(FTE) then added up to total for each duration at a given date.

I want a working chart/table showing sum of workers for the next 4 weeks. But because all the projects have different duration and start/end date, I need the total # of workers allocated for the duration of all the projects at any given date.

If you are looking at the chart I want to create a chart/table or whatever to show total of orange value that "today" line falls under (so right now for "today" I would need a number 8[3+5]). so 8 8 8 until project c starts then 16[3+5+8] and so forth.

Hope this makes more sense.

Thank you again

5. ## Re: Vertical total of Horizontal Bar Chart value or total number between lots of range dat

I think what is confusing me is the 16 starting on 8/28. Prior to that, I can see the 3+5=8. On 8/28, I see 3+5+3=11. I don't see where the 8 is coming from in the 3+5+8=16 calculation that you are doing.

In any case, a SUMIFS() function seems like it should work. It is just a matter of figuring out exactly what values you are summing.

6. ## Re: Vertical total of Horizontal Bar Chart value or total number between lots of range dat

okay, it seem that I didn't know how to upload files properly and the updated file wasn't loaded. Sorry about the confusion. I have reloaded the file for clarity.

If I were to use the Sumifs, how can I find the sum of workers on everyday of the week lets say starting Aug 25 to Sept 14? I can't figure out how to input the start and end date of projects while taking the sum of workers on given day.

Thank you

7. ## Re: Vertical total of Horizontal Bar Chart value or total number between lots of range dat

Did you try the SUMIFS() function I proposed in post #2? As near as I can tell, it looks like that should work.

8. ## Re: Vertical total of Horizontal Bar Chart value or total number between lots of range dat

I tried doing this but only got error.
But I did exactly as you said then used it to chart and it worked!!!!!!
Spent days trying to figure this out.

Thank you so much!!!!!!!!!!!!!!!!!!!!!!!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)