CODEWITHSUNDEEP
excelexcel-formula
Inorry
Inorry

Reputation: 11

How to make an IF function in Excel to sum the amounts guests that show between certain times?

Picture of mock data in Excel Doc I'm currently making an excel document for the company I work for, and I can't seem to figure out how to format this cell, to sum up, the number of guests that show for each function within a certain time frame. We have 3 separate 'shows' being:

Show 1 - is between times 10:00 am and 1:30 pm
Show 2 - is between 1:45 pm and 5:00 pm
Show 3 - is between 5:15 pm and 8:15 pm

There can be multiple parties in each show so let's say there is a party at 10:45 am with 20 people, and a party at 12:00 pm with 67 people. The guest counts for party 1 and party 2 would be listed in Column E between cells E8 to E15. Both these parties would be within Show 1 and would need to sum the cells in Column E that fall under the times that are held in Column A between cells A8-A15.

I'm not sure if this sounds confusing or not. I could always upload a picture of my excel file and highlight the cells I'm talking about so you could get a better idea of what I'm talking about.

Thank you so much for anyone that can help me with this, it means a lot and would help me with multiple excel files I'm working on that require the function that is used to solve this.

Upvotes: 0

Views: 113

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

SUMIF (Different Formulas Version)

I have used the following time format in range A8:A15:

h:mm AM/PM

and the following three formulas in range D18:D20:

=SUMIF(A$8:A$15,"<=13:30",E$8:E$15)
=SUM(E$8:E$15)-D18-D20
=SUMIF(A$8:A$15,">=17:15",E$8:E$15)

Upvotes: 1

Chris Meurer
Chris Meurer

Reputation: 468

Excel has a pretty handy function called SUMIFS() which would work nicely here.

As an example, I've created this formula which will tell you the number of guests (from column E) whose party arrived between the times (from column A) for Show 1: =SUMIFS(E:E, A:A, ">=10:00", A:A, "<=13:30")

We can extend this formula without much effort to review more than one time, and even label the outputs, ie: ="Show 1: " & SUMIFS(E:E, A:A, ">=10:00", A:A, "<=13:30") & ", Show 2: " & SUMIFS(E:E, A:A, ">=13:45", A:A, "<=17:00") & ... etc.

I hope this helps you, welcome to SO

Upvotes: 0

Related Questions