SUMIF Array Formula with "GREATER THAN"

2
votes

I know this may work:

=ArrayFormula(sum(SUMIF(D2:D9&F2:F9,J2:J3&H2,E2:E9)))

But I don't know how to find any solution for this

=ArrayFormula(sum(SUMIF(D2:D9&F2:F9,J2:J3&(">"&H2),E2:E9)))

Basically, I want to SUMIF with multiple criterias with array formula. But I can't find a way with criteria that greater than something

this is the sample case: https://docs.google.com/spreadsheets/d/1lyPSurAudZOAn2HHGPaKcgmwso46f3K4dVYA6dwlDjM/edit#gid=0

the case is about summing the quantity given from each activity, given some range of date.

  1. array formula is needed since I want the list of activity to be flexibly added, without me having to edit the formula.
  2. as far as I know, sumifs cant be used because sumifs doesn't work with array formula
3
regexif-statementgoogle-sheetssumarray-formulas
Have you considered SUMIFS()? - Variatus
You do not need array formula while SUMIFS() can handle multiple criteria. Show us your sample data so that we can help you. - Harun24HR
docs.google.com/spreadsheets/d/… that is the link. actually yes, i know SUMIFS. but i need sumif because in my case, i need to use array formula. The point is, is there any way for me to use SUMIF array formula with "greater than" criteria?? I want to sum the quantity of my activity (with activity as criteria in a collection of ranges), that is greater than given date. The actual case can be seen in the link - Randy Adikara

3 Answers

3
votes

try:

=ARRAYFORMULA(SUM(IF((F2:F>H2)*(REGEXMATCH(D2:D, 
 TEXTJOIN("|", 1, J2:J))), E2:E, )))

0

1
votes

or try:

=SUM(FILTER(E2:E, REGEXMATCH(D2:D, TEXTJOIN("|", 1, J:J)), F2:F>H2))

0

0
votes

Some alternative:

sumif and vlookup==>

=arrayformula(sumif((1-isna(vlookup(D2:D9,J2:J3,1)))*F2:F9,">" & H2,E2:E9))

sum if and vlookup:

=arrayformula(sum(if((1-isna(vlookup(D2:D9,J2:J3,1)))*F2:F9>H2,E2:E9,0)))

Sum and vlookup:

=arrayformula(sum((iferror(vlookup(D2:D9,J2:J3,1),"")=D2:D9)*(F2:F9>H2)*E2:E9))