**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.*