calculate % increase by date range with multiple worksheets

**rogrand** · 06-27-2020, 12:40 AM

Much information but not one that fits my scenario.

I have a worksheet that calculates % completed as staff enters "Yes" from the total of three groups "A", "B", "C" on different dates.

Trying to find a combo formula that will give me the individual % INCREASE for A, B, C and total % INCREASE by "******date range******** Date range is the key.

My difficulty is the multiple formula and worksheet that will work together to get it done.

Ultimately this will be used to create a dashboard to see progress on an application being taught and used for our patients. We have multiple Hospital

Attached is an example of two. Your help will be appreciated.

**AliGW** · 06-27-2020, 01:11 AM

Which cell will contain the formula? Where are the manually mocked up results?

**Dolphin Revanth** · 06-27-2020, 01:17 AM

Hi,

I have found a solution for you. Kindly refer the attachment. Thank you.

1.Use SUMIFS formula to sum the percentages in range with three criterias as shown below
=SUMIFS(Table13[% of Total for facility],Table13[Tier],$M6,Table13[Trained and device setup complete?
Yes],"yes",Table13[TimeStamp],"<=02/06/2020")-SUMIFS(Table13[% of Total for facility],Table13[Tier],$M6,Table13[Trained and device setup complete?
Yes],"yes",Table13[TimeStamp],"<=01/06/2020")

2. Change the date for the next colum.

Thank You

**AliGW** · 06-27-2020, 01:20 AM

Sorry for off-topic interjection:

Dolphin Ravanth

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

**Dolphin Revanth** · 06-27-2020, 01:44 AM

Sure AliGW. Hereafter, I will provide details in the thread also. Thank you.

**AliGW** · 06-27-2020, 01:46 AM

I'd like you to add the requested detail this time, too - you can edit post #3 to do this. Thank you.

**rogrand** · 06-29-2020, 09:28 AM

Originally Posted by Dolphin Revanth

Hi,

I have found a solution for you. Kindly refer the attachment. Thank you.

1.Use SUMIFS formula to sum the percentages in range with three criterias as shown below
=SUMIFS(Table13[% of Total for facility],Table13[Tier],$M6,Table13[Trained and device setup complete?
Yes],"yes",Table13[TimeStamp],"<=02/06/2020")-SUMIFS(Table13[% of Total for facility],Table13[Tier],$M6,Table13[Trained and device setup complete?
Yes],"yes",Table13[TimeStamp],"<=01/06/2020")

2. Change the date for the next colum.

Thank You

Good Morning Dolphin,
It looks great even though there are so much in the formula, however, when I add a new date (for example under Column K there represents Tiea A, it zero's all calculations.

**JeteMc** · 06-29-2020, 10:03 PM

Due to regional settings I feel that the following change will be needed:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**bebo021999** · 06-29-2020, 10:28 PM

To avoid regional setting issue, use DATE:

Please Login or Register  to view this content.

Change the 2 dates for next column

**rogrand** · 06-29-2020, 10:55 PM

Originally Posted by JeteMc

Due to regional settings I feel that the following change will be needed:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

I think I muddered the waters by haveing the column "% of Total for facility". The formula is giving me error. I am trying to ajust the formula to include the "Tier" column and the "time stamp" column with out success.

**AliGW** · 06-30-2020, 01:11 AM

Post a workbook showing what you have tried.

**rogrand** · 06-30-2020, 12:40 PM

Originally Posted by bebo021999

To avoid regional setting issue, use DATE:

Please Login or Register  to view this content.

Change the 2 dates for next column

Expected result is : 9% in the "report" sheet cell B17

I have tried both of the below formula with error:

=SUMIFS(Table14[% of Total for facility],,$J6,Table14[Trained and device setup complete?
Yes],"yes",Table14[TimeStamp],"<="&B1)-SUMIFS(Table14[% of Total for facility],Table14[Tier],$J6,Table14[Trained and device setup complete?
Yes],"yes",Table14[TimeStamp],"<="&b2)

=COUNTIFS(Westside!,West[TIME STAMP],Report!">="&B1,WESTSIDE!,West[TIME STAMP],Report!"<="&B2,REPORT!,A18/COUNTIFS(REPORT!,A18))

=COUNTIF(Table14[Trained and device setup complete?
Yes],"=Yes",Table14[Time Stamp],"<=Report!B1,Table14[Time Stamp],">=Report!B2/COUNTIFS(Table14[Time Stamp)

I have included the workbook.

**JeteMc** · 06-30-2020, 03:02 PM

Removing the circular reference by changing the formulas in the time stamp column to values and then removing the data validation from the column header "Trained and device setup complete?" may have helped some.
It seems as if the first formula could read:

Formula:

Please Login or Register  to view this content.

However it returns 83% as opposed to 9%. Perhaps if we knew why it should return 9% we could be more helpful.
Let us know if you have any questions.

**rogrand** · 06-30-2020, 03:16 PM

Originally Posted by JeteMc

Removing the circular reference by changing the formulas in the time stamp column to values and then removing the data validation from the column header "Trained and device setup complete?" may have helped some.
It seems as if the first formula could read:

Formula:

Please Login or Register  to view this content.

However it returns 83% as opposed to 9%. Perhaps if we knew why it should return 9% we could be more helpful.
Let us know if you have any questions.

Actually, the result should be 2%. Total of 439 items if you count from the "Tier" column and only 8 items fall within the date range of 6/29 to 7/10.
this would be 2%

**JeteMc** · 06-30-2020, 04:51 PM

To get 2% try: =COUNTIFS(Table14[Trained and device setup complete?],"Yes",Table14[Time Stamp],">="&B1,Table14[Time Stamp],"<="&B2)/COUNTIFS(Table14[Time Stamp],"<>")
Please don't quote whole posts, just use Quick Reply.
Let us know if you have any questions.

**rogrand** · 06-30-2020, 04:57 PM

Unfortunate its not calculating correctly. I get a "0" as a result when I have items within the range specified.

Yes 6/30/20
Yes 6/30/2020
Yes 6/30/2020
Yes 6/30/2020
Yes 6/30/2020
Yes 6/30/2020
Yes 6/30/2020
Yes 6/30/2020
Yes 8/4/2020

**JeteMc** · 06-30-2020, 05:03 PM

Here is the file with the formula applied to cell B17.
Be sure that the format is set to percentage (decimal places 0)
Let us know if you have any questions.

**rogrand** · 06-30-2020, 05:25 PM

It works. Many thanks. Wounder why it was not copying pasting correctly.
If you don't want to response its ok. But is there $ need to use to carry over the formula to other cells. If so where does it get inserted on the formula?

**JeteMc** · 06-30-2020, 05:55 PM

If you mean copying the formula across row 17 to cell AB17 then try:

Formula:

Please Login or Register  to view this content.

Structured references do not use the $ sign for locking the columns when copied across.
Let us know if you have any questions.

**rogrand** · 06-30-2020, 05:55 PM

Never mind, I found the answer, its by duplicating the []. For example [[Time Stamp]:[Time Stamp]]

Thank you for your assistance. And just to let you know this will effect patient care and you contribution is appreciated.

**JeteMc** · 06-30-2020, 05:57 PM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you and your patients have a blessed day.

calculate % increase by date range with multiple worksheets

LinkBack

Thread Tools

Rate This Thread

Display

calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Re: calculate % increase by date range with multiple worksheets

Thread Information

Users Browsing this Thread

Similar Threads

Auto Calculate based on the multiple conditions including date range

[SOLVED] Calculate Mid Year Pay Increase Based on Anniversary Date

Formula to total amounts on multiple worksheets for particular date range

[SOLVED] Formula to calculate date of step pay increase.

How to sum totals from a date range across multiple worksheets?

How to calculate the quarterly increase given a yearly percentage increase

Filter for date range across multiple columns and multiple worksheets

Bookmarks

Bookmarks

Posting Permissions