SOLVED

# =SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEM_WORKDAYS) problem

Occasional Contributor

# =SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEM_WORKDAYS) problem

Hello, so I have been following the tutorial of making a gantt sheet by The Office Lab (link:https://www.youtube.com/watch?v=OizqFlMtZLQ) and I ran into an error at around 2:55:42 when he begins to do an automatic percentage sum of the item percent's and work days. For some reason when I do the exact same code as him it is giving me an error #VALUE!. I typed in the code above in the title with my own values and rows and it will keep giving me an error when in the video his total comes out to 81%. Ive been stuck on this for a bit and its causing me to be at a standstill. If someone maybe knows why and could help that would be great!  (also I know his is on the W column and my is X. Its because I wanted extra space for other areas so that should not have any change towards solution)Here is my side of the code

11 Replies

# Re: =SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEM_WORKDAYS) problem

Are the values in column R really numbers? Please check with ISNUMBER().

# Re: =SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEM_WORKDAYS) problem

@LilWraith The formula shown in the formula bar in the screenshot should work. Check that cell R19 is empty. In case you have accidentally entered a space, the formula will throw a #VALUE! error.

With a space in the selected cell:

and without:

# Re: =SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEM_WORKDAYS) problem

So I went in to make sure that all my cells were just the number and am still receiving the same error. The main problem I think is from the combining of R16:R19 and X16:X19 but you would probably know better than me

# Re: =SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEM_WORKDAYS) problem

I checked my R column and when I checked each row individually they came out true except for the blank row. When I check all of them together though I receive an error. I also checked my X column and had the same result.

# Re: =SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEM_WORKDAYS) problem

Please check whether it is really blank or text with ISTEXT().

# Re: =SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEM_WORKDAYS) problem

Wow I didn't know those were tools to use that actually helps a lot! Yes, R:19 is blank and all the rows in both columns show false for ISTEXT(). I also used the ISBLANK() on R:19 and got TRUE. Sadly it is still showing an error.

# Re: =SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEM_WORKDAYS) problem

Here are some extra screenshots on the error and hopefully they may help a bit better?

# Re: =SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEM_WORKDAYS) problem

Well, then it should...must work.

# Re: =SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEM_WORKDAYS) problem

I know, that's why I'm so confused.
best response confirmed by LilWraith (Occasional Contributor)
Solution

# Re: =SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEM_WORKDAYS) problem

Except if you are using an older version of Excel. Excel 365 and Excel 2021 will work.
In older version you have to use the CTRL-SHIFT-ENTER keystroke instead of just ENTER.

# Re: =SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEM_WORKDAYS) problem

Oh my! You're a life saver! It works perfectly now!