CODEWITHSUNDEEP
excelfunctiongoogle-sheets
SophieD
SophieD

Reputation: 31

Using SUMIFS or FILTER to sum with text and date criteria, in Google Sheets

I am really stuck trying to sum a value using multiple filters, one for text, and one for the year.

I have tried two different formulas.

=SUMIFS('Libya civcas LIVE duplicate'!AD:AD, 'Libya civcas LIVE duplicate'!L:L, "*GNA*", 'Libya civcas LIVE duplicate'!B:B, "*2012*")

I thought SUMIFSwould work but I can't get it to recognise the year as the range it formatted as date not plain text. So I tried without luck the option below:

=SUM(FILTER('Libya civcas LIVE duplicate'!$AC$2:$AC,YEAR('Libya civcas LIVE duplicate'!$A$2:$A)=2012), REGEXMATCH("*GNA*", 'Libya civcas LIVE duplicate'!$K$2:$K))

The data I am working with looks like the following:

(Column A)
DATE
13/09/2012
10/08/2013
11/08/2013
20/08/2013

(Column K)
BELLIGERENTS
LNA/GNC
GNC
Unknown

(Column AC)
STRIKES
2
2
1
3

Help much appreciated. If you need further details just ask!

Upvotes: 0

Views: 1315

Answers (2)

user4039065
user4039065

Reputation: 

=sumifs(AC$2:AC, K$2:K, "*gnc*", A$2:A, ">="&date(2012, 1, 1), A$2:A, "<"&date(2013, 1, 1))

enter image description here

=SUMIFS(AC:AC, K:K, "*gnc*", A:A, ">="&DATE(2012, 1, 1), A:A, "<"&DATE(2013, 1, 1))

enter image description here

Upvotes: 3

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

You always use Sumproduct() to handle multiple criteria. For this data:

enter image description here

We calculate the sum of strikes for "birds" on 12 June 2018 using:

=SUMPRODUCT(--(A2:A25=DATE(2018,6,12))*(K2:K25="bird")*(AC2:AC25))

I believe Filter() is only supported in google-spreadsheets. From your example, it appears that you also want to apply wildcards to one of the criteria. To do this with Sumproduct(), use something like:

=SUMPRODUCT(--(A2:A25=DATE(2018,6,12))*ISNUMBER(SEARCH("bird",K2:K25,1)*(AC2:AC25)))

Upvotes: 1

Related Questions