Totals From SUMIFS Displaying Incorrectly Within Sheets and Dashboards

zbr

Hi all,

Wondering if anyone has any ideas or insight into data not displaying properly within graphs on a dashboard. The issue I experience is that data displays during the wrong 'time' or is assigned to the wrong criterion.

Here's the issue:

I have a data sheet that gets multiple form submissions per hour - sometimes multiple submissions within the span of a few minutes. Another sheet is set up to pull out data using functions to compute various KPIs. The KPI sheet then feeds into the dashboard to visualize data.

The data sheet collects the created time of form submissions and then associates an hour value to the row using three columns:

End Time

=MID([Form Submission Time)]@row, 10, 5)

Created Hour

=IF(LEN([End Time]@row) < 5, LEFT([End Time]@row, 1), LEFT([End Time]@row, 2))

Hour Value

=VALUE([Created Hour]@row)

In the KPI sheet, I then use SUMIFS to pull out any rows that that match my desired criteria. For example:

=SUMIFS({Orders}, {Hour Value}, "10", {Date}, WORKDAY(TODAY(), -1))

OR

=SUMIFS({Lines}, {Hour Value}, "1", {Date}, WORKDAY(TODAY(), -1))

So for the second example I am saying find me the sum of lines that occurred yesterday/last workday during 1:00-1:59 PM

The KPI sheet should look like this:

However, more often than not will look like this:

As such, when the dashboard visualizes and displays the data, it will often show the "wrong" data, as if it has been assigned to the wrong row (or rather criterion). The screenshot below was taken at 11:35 AM. Obviously data occurring during 1:00-2:00 PM should not be 'possible'.

The second screenshot shows what the graph should look like.

The dashboard is set to auto-refresh every minute however will display data for a time that has not yet occurred in the day. Very seldom will it refresh and display the correct visualization of data on its own - I am able at times to go into the data collection sheet and make a change to make it savable, save the sheet, exit the sheet, and then refresh the dashboard and it will then display correctly in the dashboard.

I have other data sets within the KPI sheet that pull sums for various data ranges and still experience the issue with how the totals are assigned to specific sums. This issues always occurs in a manner that negates the 10 to 1 section of the graph.

I currently have two hypotheses..

1. My functions are wrong in someway and causing the sheet to somehow interpret values containing a 1 (so 10, 11, 12) as being "1" and counting them in the SUMIF
2. The data sheet currently has 3,000 rows with more being entered randomly throughout the day at inconsistent intervals. Given that the dashboard auto refreshes every minute, there may be an issue with the functions being able to interpret that much data and spit something out to the dashboard correctly.

Any thoughts or input that you have would be sincerely appreciated and I apologize for the long post!

Paul Newcome

Ok. I typically only see this being an issue when dates are involved. I can't say that I've seen it throw off the hour for a specific chunk of time though. We may have to try a few things to see if we can get something working...

So I don't think it is actually showing 1pm. I think that the backend being 3 hours behind (Seattle time zone) is registering the LEN as less than 5 which only pulls in that first digit.

Lets try this in place of the [Created Hour]...

=LEFT([Created Time]@row, FIND(":", [Created Time]@row) - 1)

If this doesn't fix the issue then it will at least help us confirm that that is the actual issue and not something else.

Paul Newcome

Is it always the same wrong timeframe being shown, or is there some kind of consistency to it that may indicate a time zone issue?

zbr

Hi @Paul Newcome!

It's always the same timeframe where nothing will display in the 10:00-11:00, 11:00-12:00, and 12:00-1:00 'slots'. I initially thought it might be something to do with a time zone issue. However, I do not think that's the case given that the functions are just pulling out a numeric value from the created time for the criteria of the SUMIFS.

Paul Newcome

Are you able to provide a screenshot of the source data?

zbr

@Paul Newcome I sincerely appreciate you looking into this! Let me know if there is anything else I can get you.

Paul Newcome

Do you have anything where the rows are wrong? A few of the rows that would fall within those timeframes that aren't showing up in your graph?

zbr

Not sure what you mean by wrong? The form has two 'times' that are collected. An end time which is collected via a Created Time column and a start time which is manually filled out as a field within a form. Each row will always have an end time associated to it due to it being submitted via form and the Created column working it's magic. Any time a form does not have a manual start time entered is due to a temp employee completing the work (we don't have them fill out a start time). This may be what you're referring to with things not showing up.

Regardless of the presence of a start time, the SUMIFS pull a numeric value from the Created Time meaning that there should always be a value that represents when the work was complete. This gives me values of 6, 7, 8, 9, 10, 11, 12, 1, 2, 3 which are then used as criteria within each of my SUMIFS. The SUMIFS will then look for the sum of all orders, items, quantity, etc. that are associated to the corresponding numeric value and that have occurred within the desired date range.

*I also just noticed that the screenshots show the created time as 8:49 for every row, this is because I made a copy of the original sheet, usually there is greater variation in time*

Paul Newcome

Right. Sorry. I meant a row where the time is within one of those groups that's not showing up on the chart.

zbr

@Paul Newcome

The problem persists regardless of row and only for "10", "11", and "12". I have multiple different date ranges that still pull these criteria and it just refuses to show any values for the three numbers.

I have tried changing the functions with the data sheet to add symbols next to the number so 7 turns into &7 and 10 turns into &10 and so on. The SUMIFS will return values for everything but 10, 11, and 12.

I have also tried to use an @row reference as my criterion for the SUMIFS to no success. As you can see in the below screenshot, I have a test column to the left with the numbers 9 and 10. A value populates for "9" but not for "10". It is 10:32 and I know I should have data showing up for 10:00-11:00 but nothing populates. The data will skip over 10, 11, and 12 and fall under 1:00-2:00.

zbr

Sorry for the back and forth. Was just sitting with the data sheet open and watching it refresh and it did something peculiar. When it refreshed it showed "1" for my Hour Value when it should be "10".

A minute or two after it refreshed and displayed corresponding values.

Dashboard is now also visually correct:

My functions are as follows...

My Created Time column pulls the time from the created time column with:

=MID([Created (End Time)]@row, 10, 5)

My Created Hour column pulls out the hour value using this:

=IF(LEN([Created Time]@row) < 5, LEFT([Created Time]@row, 1), LEFT([Created Time]@row, 2))

I then use the Hour Value column to give me a numeric value:

=VALUE([Created Hour]@row)

This is then used as the SUMIFS. Regardless, I will switch SUMIFS criteria to utilize Created Hour to try to prevent the issue described above. Will update if this works consistently!

zbr

UPDATE: still not consistent utilizing the other column. I have tried multiple functions that pull out the one or two characters of text that document the hour to use as criteria however any value of 10, 11, or 12 will be displayed as "1" intermittently throughout the day. So I have determined what causes the issue, just not how to prevent it.

zbr

After doing some additional research looks like @Paul Newcome was right, looks like this is indeed a timezone issue.

Paul Newcome

To account for the time zone issue, you would use an IF statement to say that if it is one of the problematic hours then output whatever it should actually be, otherwise run the existing formula.

zbr

@Paul Newcome I know I've bug you a ton already but any help with what this would look like? My problematic hours seem to be 10, 11, 12 that will intermittently display the value as 1. Would this be one massive IF statement or and IF that runs off inputs from helper columns?

Paul Newcome

Assuming it is 10am - 12pm?

zbr

@Paul Newcome Yes, problem hours are 10 AM - 12 PM that will display as 1 PM when I pull the value from the created time. So created column will display the correct time but the value pulled will show as 1. My timezone is Central (CST) if that helps at all.