Question description:
This user has given permission to use the problem statement for this
blog.
I need to sum the number of hours per month one of three halls are being rented
Solved by A. F. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
12/04/2018 - 03:49
Hi there!
User
12/04/2018 - 03:49
Hello
Excelchat Expert
12/04/2018 - 03:49
I'd love to help you with your SUM formula. Are you able to give me an example of the data you're working with?
User
12/04/2018 - 03:50
Yes, hold on and I'll copy it in
Excelchat Expert
12/04/2018 - 03:50
Fantastic - thanks
User
12/04/2018 - 03:51
Basically I need to sum B (hours) within the specified month (A) and separate it into the three different rental areas (C)
User
12/04/2018 - 03:51
I'll show you some of my past attempts
User
12/04/2018 - 03:52
F1 is not considering hall, however
Excelchat Expert
12/04/2018 - 03:52
ok sure thing. Just one question - what is the specified month going to be?
User
12/04/2018 - 03:53
In F1 it is January, but I will need to be able to copy this for all 12 months
User
12/04/2018 - 03:53
For which I can change the 1 to a 2, etc
Excelchat Expert
12/04/2018 - 03:54
Awesome that you used MONTH - very nice!
User
12/04/2018 - 03:54
I've also tried using SUMIFS but to no avail
User
12/04/2018 - 03:55
The criteria I've tried using for hall consists of "*upper*", "*lower*", "*kitchen*" and "*main*"
User
12/04/2018 - 03:57
F5 is an example of how I tried to incorporate the hall as a criteria as well
User
12/04/2018 - 04:00
I've also tried using SUM(INDEX(MATCH but since they INDEX and MATCH only return one result (unless 0 is in the column or row criteria) it doesn't actually end up summing anything
Excelchat Expert
12/04/2018 - 04:00
The F5 formula is so close I think..
User
12/04/2018 - 04:00
Yeah I can't quite get my finger on the problem though haha
User
12/04/2018 - 04:01
I tried ISODD as well instead of ISNUMBER
User
12/04/2018 - 04:01
and I tried TRUE and FALSE as the results
User
12/04/2018 - 04:03
Not sure if it helps to keep giving you wrong formulas but this was another attempt of mine before I tried MONTH (putting it in F6)
User
12/04/2018 - 04:05
This was extremely close, however it Excel defaults to the present year if it's not included, so any dates not in the current year don't work
User
12/04/2018 - 04:06
Oh, I didn't know this was a timed session. If we can't figure it out what will happen?
Excelchat Expert
12/04/2018 - 04:06
it can reset one more time!
User
12/04/2018 - 04:06
Okay excellent
Excelchat Expert
12/04/2018 - 04:07
if we can't solve, it can be reposted for sure
User
12/04/2018 - 04:07
Great, thanks
User
12/04/2018 - 04:10
My only problem with hard coding in those numbers in F6 is that it will not be transferable to future years (sorry i might not have specified this)
User
12/04/2018 - 04:10
Otherwise I could just hard code in the year in the date
Excelchat Expert
12/04/2018 - 04:11
right - gotcha that makes sense. I just wanted to get F6 working or to test it rather.. so that is working right there.. lets try and automate that part to be a single month entry like your initial formula
User
12/04/2018 - 04:13
Okay I almost deleted everything so I'll let you try and work it out, I'm still pretty stuck lol
Excelchat Expert
12/04/2018 - 04:19
hey!
Excelchat Expert
12/04/2018 - 04:19
check that out
User
12/04/2018 - 04:19
Hey that's nifty
Excelchat Expert
12/04/2018 - 04:19
COOL you can just change the month in cell J5!
User
12/04/2018 - 04:20
Would I be able to change the J5 reference to something like "*upper*"?
Excelchat Expert
12/04/2018 - 04:20
dont type the month in though
Excelchat Expert
12/04/2018 - 04:20
change the date actually in teh formula bar for that cell
Excelchat Expert
12/04/2018 - 04:20
Yes totally (J6 though)
User
12/04/2018 - 04:20
yes, sorry
Excelchat Expert
12/04/2018 - 04:20
Give this a test and let me know if this will work as a solution for you
Excelchat Expert
12/04/2018 - 04:21
and I'm SO sorry this took so long.
User
12/04/2018 - 04:21
Sure give me a second
User
12/04/2018 - 04:21
Thats ok!
User
12/04/2018 - 04:21
I know it's not an easy task
Excelchat Expert
12/04/2018 - 04:21
You really know your stuff...
User
12/04/2018 - 04:21
Thank you!
User
12/04/2018 - 04:23
Does the format of J5 need to be that way?
User
12/04/2018 - 04:24
I see in the formula bar it is 4/1/2017 but it displays April
Excelchat Expert
12/04/2018 - 04:24
I'm almost certain yes
Excelchat Expert
12/04/2018 - 04:24
"mmmm"
User
12/04/2018 - 04:24
How can Id o that?
User
12/04/2018 - 04:24
TEXT(J5, "mmmm")?
Excelchat Expert
12/04/2018 - 04:24
J5 is already in mmmm
User
12/04/2018 - 04:24
How can I do that on my sheet?
Excelchat Expert
12/04/2018 - 04:25
do you mean make a cell the "mmmm" format?
User
12/04/2018 - 04:25
Yes
Excelchat Expert
12/04/2018 - 04:25
go to format cell
Excelchat Expert
12/04/2018 - 04:25
then
Excelchat Expert
12/04/2018 - 04:25
from the Number tab, select custom
Excelchat Expert
12/04/2018 - 04:26
and then manually key mmmm as the format
User
12/04/2018 - 04:26
oh lol got it
Excelchat Expert
12/04/2018 - 04:26
Oh perfect
Excelchat Expert
12/04/2018 - 04:27
EOMONTH is a cool function
User
12/04/2018 - 04:27
So this is kind of a bump in my road
User
12/04/2018 - 04:28
Also yes I never really looked at EOMONTH, good find
Excelchat Expert
12/04/2018 - 04:28
what is the bump?
User
12/04/2018 - 04:28
but basically, the information I have pasted here is taken directly from a program that imports information from my Google Calendar
User
12/04/2018 - 04:28
so the format is mostly non-negotiable
User
12/04/2018 - 04:28
however...
User
12/04/2018 - 04:29
It formats the "hours" column in a hh:mm:ss format
User
12/04/2018 - 04:29
so when I apply our new formula, my result is
User
12/04/2018 - 04:29
1944:00
Excelchat Expert
12/04/2018 - 04:29
hmmm i see
Excelchat Expert
12/04/2018 - 04:29
one sec...
Excelchat Expert
12/04/2018 - 04:31
is the answer 81 hours by chance?
User
12/04/2018 - 04:31
I believe so, yes
User
12/04/2018 - 04:31
Before we had "30" in the "just in case" it was 81
Excelchat Expert
12/04/2018 - 04:32
cool... yes
Excelchat Expert
12/04/2018 - 04:32
so the cell that you have the formula in...
Excelchat Expert
12/04/2018 - 04:32
are you able to change the format of the cell that has the formula in it which brings back 1944:00?
User
12/04/2018 - 04:33
Yes! What do you think it should be?
Excelchat Expert
12/04/2018 - 04:33
number
User
12/04/2018 - 04:33
I'd like it to just be a plain old number like 81
User
12/04/2018 - 04:33
Okay awesome
Excelchat Expert
12/04/2018 - 04:33
see if it works
User
12/04/2018 - 04:33
perfect
User
12/04/2018 - 04:33
81
Excelchat Expert
12/04/2018 - 04:33
Yes!
User
12/04/2018 - 04:34
Let me try to implement this again
Excelchat Expert
12/04/2018 - 04:34
Please do
User
12/04/2018 - 04:36
Okay, so!
User
12/04/2018 - 04:36
Since the format for the date reference includes the year, I have the same problem that doesnt let me apply this to future years
Excelchat Expert
12/04/2018 - 04:38
hmmm
Excelchat Expert
12/04/2018 - 04:38
might have to add another element to the formula... that references the cell below J5
Excelchat Expert
12/04/2018 - 04:38
so you'd enter the month and the year
Excelchat Expert
12/04/2018 - 04:38
lemme see
Excelchat Expert
12/04/2018 - 04:38
like just another criteria to the SUMIFS function
User
12/04/2018 - 04:39
Right I see, so I'd just have a new cell somewhere that would be the year
Excelchat Expert
12/04/2018 - 04:40
yep exactly
User
12/04/2018 - 04:40
and the months that require the future year would be the first cell +1
Excelchat Expert
12/04/2018 - 04:40
im just trying to figure out the syntax for that last criteria
User
12/04/2018 - 04:40
Thank you
Excelchat Expert
12/04/2018 - 04:40
well you would just enter in whatever year you need the count for
Excelchat Expert
12/04/2018 - 04:45
ah i think i found it
User
12/04/2018 - 04:48
I'll copy this down in case the session ends
Excelchat Expert
12/04/2018 - 04:48
can you extend it at all or did we run out?
Excelchat Expert
12/04/2018 - 04:48
it's SO close
User
12/04/2018 - 04:48
I can't unfortunately!
User
12/04/2018 - 04:48
I will see what I can do from here
User
12/04/2018 - 04:49
thank you so much for your help
User
12/04/2018 - 04:49
It's a good start for sure
Excelchat Expert
12/04/2018 - 04:49
i wish I could've finished it!!!
User
12/04/2018 - 04:49
I know!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
