sumifs equivalent

[wynstonh]

Hi,

I am trying to replicate a simple sumifs from excel to access & struggling to make it work. The data I have is:

Ref------ Month---------- Days
1 ----------May 2018----- 12
1 ----------May 2018----- 10
2 ----------May 2018----- 11
2 ----------June 2018----- 30
3 ----------May 2018------ 13
3 ---------May 2018------- 14
3 ---------May 2018------- 15

& what I need is a totals column with the double criteria of ref & month so the output would be as follows:

Ref -------Month----------- Days----- Total
1 ----------May 2018------- 12------- 22
1 ----------May 2018 -------10------- 22
2 ----------May 2018------- 11------- 11
2 ----------June 2018------ 30------- 30
3 ----------May 2018------- 13------- 42
3 ----------May 2018------ 14-------- 42
3 ----------May 2018------ 15-------- 42

I have attempted using DSum but how should this be used with the criteria not being fixed or based on a user entered value?

Thanks for any advice

 

[Uncle Gizmo]

Hi,

I am trying to replicate a simple sumifs from excel to access & struggling to make it work. The data I have is:

Ref------ Month---------- Days
1 ----------May 2018----- 12
1 ----------May 2018----- 10
2 ----------May 2018----- 11
2 ----------June 2018----- 30
3 ----------May 2018------ 13
3 ---------May 2018------- 14
3 ---------May 2018------- 15

& what I need is a totals column with the double criteria of ref & month so the output would be as follows:

Ref -------Month----------- Days----- Total
1 ----------May 2018------- 12------- 22
1 ----------May 2018 -------10------- 22
2 ----------May 2018------- 11------- 11
2 ----------June 2018------ 30------- 30
3 ----------May 2018------- 13------- 42
3 ----------May 2018------ 14-------- 42
3 ----------May 2018------ 15-------- 42

I have attempted using DSum but how should this be used with the criteria not being fixed or based on a user entered value?

Thanks for any advice

I don't get it?

Sent from my SM-G925F using Tapatalk

 

[Minty]

Create a grouped query on the reference and month grouped and the with the days summed. Something like

SELECT Ref, [Month] , Sum(Days) as RefMonthTotal
FROM YourTable
GROUP BY Ref,[Month]

I would strongly recommend you change the field name of Month to Something else - Month is a reserved word and function in Access and it will trip you up somewhere down the line.

 

[wynstonh]

If I was to do it in excel & my data was in columns A-C my formula in D2 would be:

=SUMIFS(C:C,A:A,$A2,B:B,$B2)

that help?

 

[wynstonh]

absolutely perfect, thanks Minty!

BTW "Month" was just shorthand for my actual field name, but thanks for the advice

 

[Minty]

You are welcome!

 

[arnelgp]

You can sum withot grouping:

SELECT Ref, [Month] , DSum("Days", "yourtable","ref=" & ref & " and month=""" & month & """") as RefMonthTotal
FROM YourTable