Convert SumIfs to Access

[Foolzrailer]

Hello

I've finally figured out how to a formula in Excel to give me SumIFS of all the data I need. Now however I need to convert this to Access if that is possible.

Danish Excel formula for SumIf:

=(SUM.HVISER(B:B;A:A;">="&A2;A:A;"<"&A2+1/24))/15

Basically my Column A in Excel would be

[Table1].[Time]

and Column B would be

[Table1].[Data]

But I can't figure out how to convert it, any support on getting this correct would be much appreciated.

 

[xenou]

Not 100% sure but my first try would be:

Select
  Sum([Data])/15 as Total
From
  Table1
Where
  [Data] >= A2
  and [Data] < (A2 + 1/24)

Kind of guessing (or not guessing, really) what's in A2...

 

[Foolzrailer]

Not 100% sure but my first try would be:

Select
  Sum([Data])/15 as Total
From
  Table1
Where
  [Data] >= A2
  and [Data] < (A2 + 1/24)

Kind of guessing (or not guessing, really) what's in A2...

Gave your suggestion a go, but I can't get it to return anything, tried a few different syntaxes..

Forgot to put in what kind of data it is, my bad.
Excel Column A = Access [Ark1].[Time] is filled with a lot of Date and time 06-09-2017 16:00:00
Excel Column B = Access [Ark1].[Data] is filled with a lot of flowdata 2.67


Tried writing your code:

Select Sum([Ark1].[Data]/15) As Total
From Ark1
Where (([Ark1].[Data] >= [Ark1].[Time]) And ([Ark1].[Data] < ([Ark1].[Time]+1/24)));

 

[xenou]

For examples sake can you provide about 3-4 rows of data from column A and B ... (which would also be enough to provide an expected result as well)...
I'm not sure what the expectation here is comparing data to time (data greater than or equal to time and data less than time)...or at least in your last attempt that seems to be happening but maybe it's not supposed to...

 

[Micron]

Your data is in records in an Access table? Then you probably need a Totals query or to use DSum function. When you post your data sample, be sure to say where this data is located and an example of expected output.

 

[matthenry]

The syntax for the ‘SUMIF’ function is pretty straight forward:

‘=SUMIF(range, criteria, [sum_range])’

The ‘range’ parameter is actually the range of cells that will be evaluated by the ‘criteria’ parameter.

The ‘criteria’ parameter is the condition that must be met in the ‘range’ parameter. For instance, if our ‘range’ was a column that listed t-shirt color, a value like ‘red’ or ‘white’ could be our ‘criteria’. The ‘criteria’ value can be text, a number, a date, a logical expression, a cell reference, or even another function.

*One thing to note, however, is that any mathematical expression must be enclosed in double quotes as we will see when we cover using comparison operators.

The ‘sum_range’ parameter is optional as noted by the brackets. This simply means that if omitted, the ‘sum_range’ will default to the same cells you chose for the ‘range’ parameter.
First, we will leave out the ‘sum_range’ to see a very simple example using a single column of numerical values.

Here we have a column of numbers from B4 to B14.

First we will use ‘SUMIF’ to get the sum of all values greater than 20. Then we will use ‘SUMIF’ to get the sum of all values less than 30.

To be sure, we could easily sort our single column of values in ascending order and quickly find the first value 21 or larger and highlight all other values below it to find the sum, but let’s just go with the concept here.

“To use the ‘SUMIF’ function to find the sum of all values greater than 20 we only need 2 parameters.”


I hope this information will be helpful!
Matt Henry

 

[Joe4]

The syntax for the ‘SUMIF’ function is pretty straight forward:

‘=SUMIF(range, criteria, [sum_range])’

The ‘range’ parameter is actually the range of cells that will be evaluated by the ‘criteria’ parameter.

The ‘criteria’ parameter is the condition that must be met in the ‘range’ parameter. For instance, if our ‘range’ was a column that listed t-shirt color, a value like ‘red’ or ‘white’ could be our ‘criteria’. The ‘criteria’ value can be text, a number, a date, a logical expression, a cell reference, or even another function.

*One thing to note, however, is that any mathematical expression must be enclosed in double quotes as we will see when we cover using comparison operators.

The ‘sum_range’ parameter is optional as noted by the brackets. This simply means that if omitted, the ‘sum_range’ will default to the same cells you chose for the ‘range’ parameter.
First, we will leave out the ‘sum_range’ to see a very simple example using a single column of numerical values.

Here we have a column of numbers from B4 to B14.

First we will use ‘SUMIF’ to get the sum of all values greater than 20. Then we will use ‘SUMIF’ to get the sum of all values less than 30.

To be sure, we could easily sort our single column of values in ascending order and quickly find the first value 21 or larger and highlight all other values below it to find the sum, but let’s just go with the concept here.

“To use the ‘SUMIF’ function to find the sum of all values greater than 20 we only need 2 parameters.”


I hope this information will be helpful!
Matt Henry

Please note the forum where this post resides, and read the question carefully.
They are not asking for how to do the SUMIF function in Excel. They are asking how to do it in Access.

 

[petertenthije]

The data in the time column, is that always a full hour or can it also be other times?