Count the number of blocks made on a range of date or certain date with multiple criteria

**ebase02** · 04-28-2017, 04:05 AM

Hello excelforum. Kindly help me on this formula, I have been struggling this for a week and can't still get the right formula for it. I have browse several websites for the formula and every time I see something new I get more confused.

I'm looking for the solution for this sheet. The Summary is where results are and formula, while the Monthly Tab shows the reservation made by the member on each month and date.
https://docs.google.com/spreadsheets...it?usp=sharing

I am looking to calculate the total regular blocks, reserved blocks, and borrowed blocks.
- Regular blocks are blocks that are calculate today, and onward by the end of the month.
Regular blocks formula:

Please Login or Register  to view this content.

- Reserved blocks are blocks that are made on the previous month. (eg. Current month is April, and reserve is March).
Reserved blocks formula:

Please Login or Register  to view this content.

- Borrowed blocks are blocks that are made on the next month. (eg. Current month is April, and borrowed is May).
Borrowed blocks formula:

Please Login or Register  to view this content.

Requesting the right formula with this condition:
- Regular block - if "2" then count it as two instead of one. Right now my formula only calculate "1", and totally ignore "2" as shown in the formula.

Also would it be possible to add additional criteria in regular block and compute it this way? >=Today(), month<=()? (eg. April 28 and the end of the month only, April 30)?

- Reserved block & borrowed block - If the current reservation made on a particular date exceed his quota deduct on the reserved block first, and then if it is not enough use the borrowed block.

Example. BB has a quota of 6 per month. And can use unused reservation on the previous month if it has any, and if it is not enough, it will use the reservation for the next month.

Best regards

**JohnTopley** · 04-28-2017, 04:10 AM

Attach a sample workbook (not image!).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**ebase02** · 04-28-2017, 04:29 AM

Hello John,

Thank you. I have attached the google spreadsheet for it. I presented the formula that I have right now, mentioned my request of formula and the output that I am looking for.

**JohnTopley** · 04-28-2017, 05:28 AM

Try the following:

in B2

=SUMIFS('Monthly Tab'!$E$2:$E$376,Date,">="&TODAY(),Date,"<="&EOMONTH(TODAY(),0),Name,A2)

in E2

=6-SUM(COUNTIFS(Date,">"&EOMONTH(TODAY(),-2)+1,Date,"<="&EOMONTH(TODAY(),-1),Name,A2,RB,1))

in G3

=6-SUM(COUNTIFS(Date,">"&EOMONTH(TODAY(),0)+1,Date,"<"&EOMONTH(TODAY(),1),Name,A2,RB,1))

- Reserved block & borrowed block - If the current reservation made on a particular date exceed his quota deduct on the reserved block first, and then if it is not enough use the borrowed block.

I don't understand the above. Your formulas have 6 (as a quota value?) so I would change this to variable (column) in your table

**ebase02** · 04-28-2017, 11:06 AM

Hello John,

Thank you! I will try to use the formula that you gave and see if it is the output that I am looking for. I really do appreciate your time given in this matter. I might marked this as done, and escalate it to Commercial Services if ever I need to make another request to give you a proper credit.

Best regards

Count the number of blocks made on a range of date or certain date with multiple criteria

LinkBack

Thread Tools

Rate This Thread

Display

Count the number of blocks made on a range of date or certain date with multiple criteria

Re: Count the number of blocks made on a range of date or certain date with multiple crite

Re: Count the number of blocks made on a range of date or certain date with multiple crite

Re: Count the number of blocks made on a range of date or certain date with multiple crite

Re: Count the number of blocks made on a range of date or certain date with multiple crite

Thread Information

Users Browsing this Thread

Similar Threads

Formula to count number of times a date is 15 or 28 in a date range

[SOLVED] Count and sum with multiple criteria including date range

[SOLVED] Count if multiple criteria and within date range

[SOLVED] Count number of values dependent between horizontal date range based on 2 date values

Adding a date range to a unique data count based on multiple criteria

Count - multiple criteria, multiple sheets, and date range.

Excel 2007 : Count using multiple criteria and date range

Bookmarks

Bookmarks

Posting Permissions