Excel - SUM Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.