sumifs equivalent (1 Viewer)

wynstonh

Registered User.
Local time
Today, 22:47
Joined
Oct 27, 2016
Messages
38
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

Nifty Access Guy
Staff member
Local time
Today, 22:47
Joined
Jul 9, 2003
Messages
16,245
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

AWF VIP
Local time
Today, 22:47
Joined
Jul 26, 2013
Messages
10,355
Create a grouped query on the reference and month grouped and the with the days summed. Something like
Code:
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

Registered User.
Local time
Today, 22:47
Joined
Oct 27, 2016
Messages
38
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

Registered User.
Local time
Today, 22:47
Joined
Oct 27, 2016
Messages
38
absolutely perfect, thanks Minty!

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:47
Joined
May 7, 2009
Messages
19,169
You can sum withot grouping:

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

Users who are viewing this thread

Top Bottom